I have a function that copies and alters a large amount of data, then overwrites a sheet with the results. My code works correctly when I use range.setValues(data), but it is exceedingly slow and sometimes errors out for that reason. I have switched my code to instead use the Values.update() function, but I am encountering an odd problem.
When my code is:
const result = Sheets.Spreadsheets.Values.update(valueRange, ss.getId(), "Sheet Name", {valueInputOption: 'RAW'});
the values are copied, but there are issues with the data types (currencies being interpreted as dates, etc.). I know the solution is to change the valueInputOption. However, when I use the following code:
const result = Sheets.Spreadsheets.Values.update(valueRange, ss.getId(), "Sheet Name", {valueInputOption: 'USER_ENTERED'});
the function finishes executing without an error but sheet is filled with entirely blank cells. Any idea why this may be?
Thanks!
Reposting here so I can mark resolved: I've actually just figured out the issue! Before the above code was executing, I had a line to clear the range, using range.clearContent();. After clearing the range, I update the sheet with the data. However, Google Apps Script was waiting to clear the range until after I had written the content, so the sheet was ending up blank. I have fixed this issue by adding a SpreadsheetApp.flush(); call directly after range.clearContent(). It is now working as intended.