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.

asked Aug 2 '16 at 11:41

Tim

9316

1

A good nights rest did me some good; I was able to get it working.

– Tim
Aug 2 '16 at 11:58

add a comment |

    gapi.client.sheets.spreadsheets.values.update({

      spreadsheetId: 'something',

      range: 'Sheet1!B2',

    })

asked Aug 2 '16 at 11:41

Tim

9316

1

A good nights rest did me some good; I was able to get it working.

– Tim
Aug 2 '16 at 11:58

add a comment |

    gapi.client.sheets.spreadsheets.values.update({

      spreadsheetId: 'something',

      range: 'Sheet1!B2',

    })

asked Aug 2 '16 at 11:41

Tim

9316

    gapi.client.sheets.spreadsheets.values.update({

      spreadsheetId: 'something',

      range: 'Sheet1!B2',

    })

javascript google-sheets-api

asked Aug 2 '16 at 11:41

Tim

9316

asked Aug 2 '16 at 11:41

Tim

9316

asked Aug 2 '16 at 11:41

Tim

9316

asked Aug 2 '16 at 11:41

Tim

9316

asked Aug 2 '16 at 11:41

Tim

9316

1

A good nights rest did me some good; I was able to get it working.

– Tim
Aug 2 '16 at 11:58

add a comment |

1

A good nights rest did me some good; I was able to get it working.

– Tim
Aug 2 '16 at 11:58

A good nights rest did me some good; I was able to get it working.

– Tim
Aug 2 '16 at 11:58

add a comment |

3 Answers
3

active

oldest

votes

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 at 14:50

Ali

565820

answered Aug 2 '16 at 11:59

Tim

9316

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??

– here_to_learn
Aug 25 '16 at 17:19

@here_to_learn Are you still having trouble?

– Jonas
Oct 4 '16 at 17:39

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

– Ryan Shillington
Sep 6 '18 at 12:37

add a comment |

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 '18 at 16:34

Curtis Chong

3311721

add a comment |

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 at 16:38

tehhowch

5,45341126

answered Jan 1 at 13:11

Rizwan Haque

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

– Rizwan Haque
Jan 1 at 13:15

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f38719469%2fupdate-a-single-cell-of-google-sheets-using-javascript-api%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

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 at 14:50

Ali

565820

answered Aug 2 '16 at 11:59

Tim

9316

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??

– here_to_learn
Aug 25 '16 at 17:19

@here_to_learn Are you still having trouble?

– Jonas
Oct 4 '16 at 17:39

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

– Ryan Shillington
Sep 6 '18 at 12:37

add a comment |

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 at 14:50

Ali

565820

answered Aug 2 '16 at 11:59

Tim

9316

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??

– here_to_learn
Aug 25 '16 at 17:19

@here_to_learn Are you still having trouble?

– Jonas
Oct 4 '16 at 17:39

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

– Ryan Shillington
Sep 6 '18 at 12:37

add a comment |

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 at 14:50

Ali

565820

answered Aug 2 '16 at 11:59

Tim

9316

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 at 14:50

Ali

565820

answered Aug 2 '16 at 11:59

Tim

9316

edited Jan 27 at 14:50

Ali

565820

edited Jan 27 at 14:50

Ali

565820

edited Jan 27 at 14:50

Ali

565820

answered Aug 2 '16 at 11:59

Tim

9316

answered Aug 2 '16 at 11:59

Tim

9316

answered Aug 2 '16 at 11:59

Tim

9316

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??

– here_to_learn
Aug 25 '16 at 17:19

@here_to_learn Are you still having trouble?

– Jonas
Oct 4 '16 at 17:39

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

– Ryan Shillington
Sep 6 '18 at 12:37

add a comment |

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??

– here_to_learn
Aug 25 '16 at 17:19

@here_to_learn Are you still having trouble?

– Jonas
Oct 4 '16 at 17:39

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

– Ryan Shillington
Sep 6 '18 at 12:37

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??

– here_to_learn
Aug 25 '16 at 17:19

@here_to_learn Are you still having trouble?

– Jonas
Oct 4 '16 at 17:39

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

– Ryan Shillington
Sep 6 '18 at 12:37

add a comment |

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 '18 at 16:34

Curtis Chong

3311721

add a comment |

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 '18 at 16:34

Curtis Chong

3311721

add a comment |

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 '18 at 16:34

Curtis Chong

3311721

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 '18 at 16:34

Curtis Chong

3311721

answered Aug 3 '18 at 16:34

Curtis Chong

3311721

answered Aug 3 '18 at 16:34

Curtis Chong

3311721

answered Aug 3 '18 at 16:34

Curtis Chong

3311721

add a comment |

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 at 16:38

tehhowch

5,45341126

answered Jan 1 at 13:11

Rizwan Haque

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

– Rizwan Haque
Jan 1 at 13:15

add a comment |

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 at 16:38

tehhowch

5,45341126

answered Jan 1 at 13:11

Rizwan Haque

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

– Rizwan Haque
Jan 1 at 13:15

add a comment |

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 at 16:38

tehhowch

5,45341126

answered Jan 1 at 13:11

Rizwan Haque

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

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

 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 at 16:38

tehhowch

5,45341126

answered Jan 1 at 13:11

Rizwan Haque

edited Jan 2 at 16:38

tehhowch

5,45341126

edited Jan 2 at 16:38

tehhowch

5,45341126

edited Jan 2 at 16:38

tehhowch

5,45341126

answered Jan 1 at 13:11

Rizwan Haque

answered Jan 1 at 13:11

Rizwan Haque

answered Jan 1 at 13:11

Rizwan Haque

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

– Rizwan Haque
Jan 1 at 13:15

add a comment |

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

– Rizwan Haque
Jan 1 at 13:15

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

– Rizwan Haque
Jan 1 at 13:15

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu