CODEWITHSUNDEEP
javascriptgoogle-sheets-api
Tim
Tim

Reputation: 323

Update a single cell of Google Sheets using JavaScript API

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.

Upvotes: 8

Views: 10601

Answers (3)

Tim
Tim

Reputation: 323

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

Upvotes: 13

Rizwan Haque
Rizwan Haque

Reputation: 11

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); });

Upvotes: 0

Curtis Chong
Curtis Chong

Reputation: 811

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);
    }
});

Upvotes: 7

Related Questions