Using Sheets.Spreadsheets.Values.update() Correctly

Question

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?

Tanaike · Accepted Answer · 2024-07-19 01:42:27Z

1

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:

answered Jul 19, 2024 at 1:42

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DanCue Over a year ago

Ugh... that worked. What I wasn't understanding was that leaving it like { updatedArr } meant I was saying { updatedArr: updatedArr }. If I would have had var values = updatedArr then I could do { values } right?

DanCue Over a year ago

Yes. That report was very useful. Thank you for that. I'm starting to convert my getValues() and setValues() on my projects where I'm dealing with a lot of values. This helps me confirm my suspicions and makes me feel better about that decision.

Tanaike Over a year ago

@DanCue Thank you for replying. About If I would have had var values = updatedArr then I could do { values } right?, I think that your understanding is correct. I'm glad your issue was resolved.

Collectives™ on Stack Overflow

Using Sheets.Spreadsheets.Values.update() Correctly

1 Answer 1

Modification points:

From:

To:

Note:

References:

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

From:

To:

Note:

References:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related