API sending data from DOMO to SmartSheet does not allow formulas to work
We are using the pre-built DOMO writeback connector to send data from DOMO into SmartSheet. The data is updated daily with the intent that rows in SmartSheet today will be cleared and replaced with refreshed data tomorrow. Consider this dataset 'A'. We have another dataset 'B' that is contained within SmartSheet and has a vlookup formula to 'A'.
Every time the smartsheet 'A' refreshes via the DOMO writeback connector, the reference range breaks and the formula in SmartSheet 'B' errors. Is this expected behavior or is there a way to configure the API import that will allow DOMO to refresh data without breaking the reference range after every update?
Best Answer
-
Wow, that is a brutal way of updating a sheet.
The problem is that while people look at names, smartsheet is looking at column IDs and order.
Here's an idea:
Take the sheet that DOMO updates and set up a Data Shuttle to export the data as a csv file each day after DOMO does it's update.
Use Data Shuttle to use the csv file to update a new sheet "C" and let your dataset B do it's vlookup against this new sheet.
As long as the column names stay the same, then data shuttle should work and the column IDs on sheet "C" won't change on the import of the csv file.
/marc
Answers
-
How exactly are you creating your range(s)?
-
@Paul Newcome The sheet being reference is called 'Item Attribute import from DOMO' and I'm using the "edit reference" link in the formula builder to go to the sheet and select at the columns which results in this formula -
=VLOOKUP([Primary Column]@row, {Item Attribute import from DOMO Range 1}, 3, false)
Thanks
-
And how exactly are you selecting the columns? Are you highlighting ranges of cells or clicking on the column headers themselves?
-
@Paul Newcome I am selecting the first column header, shift, then selecting the last column header so all are selected.
-
I would use the API to get a json dump of sheet A before and after the DOMO update and compare the column ID numbers/names and order before and after. And make sure the sheet ID is the same.
/marc
-
I agree with @marc4. It sounds like something is fundamentally changing with the source whether that be new columns or a new sheet entirely. I personally would start by checking the sheet ID to see if it is a new sheet being created each day.
-
@marc4 @Paul Newcome Thank you both for the suggestions. I confirmed that the sheet ID is not changing after updates. However, via the activity log, I can see that method DOMO is using to refresh the data is deleting each column individually and then inserting the same columns again.
Any thoughts on if there is a way for the vlookup formula to handle this or API documentation that can be relayed to DOMO on a better practice for updating the sheet?
-
Wow, that is a brutal way of updating a sheet.
The problem is that while people look at names, smartsheet is looking at column IDs and order.
Here's an idea:
Take the sheet that DOMO updates and set up a Data Shuttle to export the data as a csv file each day after DOMO does it's update.
Use Data Shuttle to use the csv file to update a new sheet "C" and let your dataset B do it's vlookup against this new sheet.
As long as the column names stay the same, then data shuttle should work and the column IDs on sheet "C" won't change on the import of the csv file.
/marc
-
I agree with @marc4. That seems like a pretty goofy way to run an update.
I also agree that 2 separate Data Shuttle workflows (offload then upload) should solve your problem if you are unable to fix the API.
