I’ve created an array that I’d like to have pasted on a sheet in cell A1. I’m aware of how to do that using setValues() but I was under the impression that using Sheets.Spreadsheets.Values.update() would be faster.
So I guess that’s my first question. Is it faster? My array, we’ll call updatedArr consists of 70K rows by 35 columns of data.
I just can’t seem to figure out how to paste this array using that method. Here is my attempt:
Sheets.Spreadsheets.Values.update({ updatedArr }, tss.getId(), `'` + clickerDataSht.getName() + `'!A1`, { valueInputOption: "USER_ENTERED" });
The error I get:
GoogleJsonResponseException: API call to sheets.spreadsheets.values.update failed with error: Invalid JSON payload received. Unknown name "updatedArr" at 'data': Cannot find field.
Is there something I need to be doing to that array?
Modification points:
{ updatedArr }is the same with{ updatedArr: updatedArr }. If you want to use “Method: spreadsheets.values.update”, the property name for the values is required to bevalues. I guessed that this might be the reason for your current issue ofUnknown name "updatedArr" at 'data': Cannot find field..
If your value of updatedArr is a 2-dimensional array, how about the following modification?
From:
Sheets.Spreadsheets.Values.update({ updatedArr }, tss.getId(), `'` + clickerDataSht.getName() + `'!A1`, { valueInputOption: "USER_ENTERED" });
To:
Sheets.Spreadsheets.Values.update({ values: updatedArr }, tss.getId(), `'${clickerDataSht.getName()}'!A1`, { valueInputOption: "USER_ENTERED" });
- By this modification, the values
updatedArris put into cell “A1” ofclickerDataSht.getName()sheet.
Note:
- About your question
Is it faster?, is this report useful? Ref (Author: me)
References:
- Method: spreadsheets.values.update
- Object initializer
3