Using Copy Sheet API to copy column data and not formulas ?

JohnI

I am trying to use the Copy Sheet API call to take a snapshot and archive on a weekly basis what has been entered into a source sheet. The source sheet has many formulas but I only want the data copied over. So far the source sheet is getting cloned over with formulas. I cannot have the copied formulas in the target sheet updating archived data.

Is there a way to call this API so only pure data gets copied over and not formulas?

Darren Mullen

@JohnL I'm not familiar with the entire Copy Sheet call in the API, but if you copy row by row, Each cell should have a "value" and a "displayValue" property. the "displayValue" property should strip out the formula?

Alex Argumedo

I couldn't find any API command to copy a whole sheet with no formulas, If it is that actually the case, and you really have to remove the formulas, you would have to do it column by column,

Get sheet

Get first row (here the formulas reside)

Recursion to get each cell, update each cell where the formula parameter is not '' with ''

Done.

The only disadvantage of this process is that is very costly, in resources and time.

This works only for column formulas, if you have different formulas all over the place, then you will have to do a recursion on every single cell of the sheet.