Update a single cell of Google Sheets using JavaScript API

Question

I've followed this tutorial and I am able to successfully get it working to "get" data from the spreadsheet, but now I would like to update a single cell using just the JavaScript API. I'm trying to use this JS method:

    gapi.client.sheets.spreadsheets.values.update({
      spreadsheetId: 'something',
      range: 'Sheet1!B2',
    })

But I'm not quite sure how to pass in the new cell value into the Request body. I've tried my best to follow this as a guide but I'm not quite sure how to use this. I can't find any documentation around "gapi.client.sheets.spreadsheets.values.update" JavaScript API. I get 400 errors back from the API with anything I've tried.

A good nights rest did me some good; I was able to get it working. — Tim
– Tim, Commented Aug 2, 2016 at 11:58

Ali Nazari · Accepted Answer · 2019-01-27 14:50:55Z

13

gapi.client.sheets.spreadsheets.values.update({
    spreadsheetId: 'something',
    range: 'Sheet1!B2',
    valueInputOption: 'USER_ENTERED',
    values: [ ["123"] ]
}).then(function(response) {
    console.log(response);
});

edited Jan 27, 2019 at 14:50

Ali Nazari

1,5471 gold badge22 silver badges34 bronze badges

answered Aug 2, 2016 at 11:59

Tim

3231 gold badge2 silver badges7 bronze badges

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

2 Comments

here_to_learn Over a year ago

Hi, I am getting error code 403, permission denied with the message: The request cannot be identified with a client project. Please pass a valid API key with the request. Can you please help??

Ryan Shillington Over a year ago

@Tim this should be updated to replace "values" with "resource" as Curtis Chong has below.

Curtis Chong · Accepted Answer · 2018-08-03 16:34:26Z

7

I think google updated their API because Tim's answer didn't work for me. Here is my structure:

let spreadsheetId = "idOfMySheet";
let range = "nameOfSheet!A1";
let valueInputOption = "RAW";
let myValue = 5;

let values = [[myValue]];
let resource = {
    values,
};
sheets.spreadsheets.values.update({
    spreadsheetId,
    range,
    valueInputOption,
    resource
}, (err, result) => {
    if (err) {
        console.log(err);
    } else {
        console.log('%d cells updated.', result.updatedCells);
    }
});

answered Aug 3, 2018 at 16:34

Curtis Chong

8112 gold badges14 silver badges27 bronze badges

Comments

tehhowch · Accepted Answer · 2019-01-02 16:38:37Z

0

Check This link

For updating sheet values, authorization is required with non-read-only OAuth scope like https://www.googleapis.com/auth/spreadsheets

and then

 return gapi.client.sheets.spreadsheets.values.update({
        "spreadsheetId": sid,
        "range": sheetName + "!A2:D",
        "includeValuesInResponse": "true",
        "responseDateTimeRenderOption": "FORMATTED_STRING",
        "responseValueRenderOption": "FORMATTED_VALUE",
        "valueInputOption": "USER_ENTERED",
        "resource": {
          "majorDimension": "ROWS",
          "range": sheetName + "!A2:D",
          "values": [['data1', 'data2', 'data3', 'data4']]
        }
      }).then(function(response) {
       console.log(response);
      
      }, function(err) { console.error("Execute error", err); });

edited Jan 2, 2019 at 16:38

tehhowch

9,9124 gold badges27 silver badges47 bronze badges

answered Jan 1, 2019 at 13:11

Rizwan Haque

114 bronze badges

1 Comment

Rizwan Haque Over a year ago

for single cell used '"range": sheetName+"!B1",'

Collectives™ on Stack Overflow

Update a single cell of Google Sheets using JavaScript API

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related