CODEWITHSUNDEEP
node.jssmartsheet-api
bowow99
bowow99

Reputation: 69

Smartsheet extract sheet ranges for reuse in another sheet - node.js

Not sure if this is possible...

I use a Smartsheet workspace as a template. One of the sheets needs to be updated monthly (based on an external body changing requirements). They kindly provide an xml file for the update. To do that I wipe the rows off the sheet and then using the XML and node.js rebuild the sheet. That's working like a charm :-)

This master sheet contains ranges for calculations on a different summary sheet. By this I mean that the calculation sheet refers to the master sheet in a formula such as:

=SUM(COUNTIFS({master sheet! Range 1}, $Primary@row, {master sheet! Range 2}, B$2), COUNTIFS({master sheet! Range 1}, $Primary@row, {master sheet! Range 2}, B$3), COUNTIFS({master sheet! Range 1}, $Primary@row, {master sheet! Range 2}, B$4), COUNTIFS({master sheet! Range 1}, $Primary@row, {master sheet! Range 2}, B$5), COUNTIFS({master sheet! Range 1}, $Primary@row, {master sheet! Range 2}, B$6))

As you can see in this single formula there are references to Range 1 and Range 2. These are system generated names (which I know I can change).

The issue is when I wipe the rows I also wipe the ranges. :-(

Is it possible to read the ranges before I wipe the sheet, and then somehow import them back into the sheet once it's updated?

Advice appreciated.

Bowow99

Upvotes: 0

Views: 280

Answers (1)

Kim Brandl
Kim Brandl

Reputation: 13500

Do you know the id of the summary sheet (i.e., the sheet that contains formulas referencing ranges in the sheet you're wiping/updating periodically)?

If so, it seems like you could run the List Cross-sheet References operation on the summary sheet -- to get the list of all cross-sheet references that the sheet uses.

Then after you've wiped data from the other sheet and added new data to it, you could run the Create Cross-sheet Reference operation on the summary sheet to create any references to the sheet where you wiped/updated data exactly as they existed before (i.e., using the data that was returned in the List Cross-sheet References response).

Note: If there are any other sheets, besides the summary sheet that use cross-sheet references to reference ranges in the sheet where you're wiping/recreating data, then you'd need to do this same process for each of those sheets as well.

Upvotes: 0

Related Questions