Using Sheets.Spreadsheets.Values.update() Correctly

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?

>Solution :

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 be values. I guessed that this might be the reason for your current issue of Unknown 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 updatedArr is put into cell "A1" of clickerDataSht.getName() sheet.

Note:

• About your question Is it faster?, is this report useful? Ref (Author: me)

References:

• Method: spreadsheets.values.update
• Object initializer