Mixing
Update a single cell of Google Sheets using JavaScript API
4
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.
javascript google-sheets-api
asked Aug 2 '16 at 11:41
TimTim
9316
add a comment |
4
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.
javascript google-sheets-api
asked Aug 2 '16 at 11:41
TimTim
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 |
4
4
4
1
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.
javascript google-sheets-api
asked Aug 2 '16 at 11:41
TimTim
9316
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.
javascript google-sheets-api
javascript google-sheets-api
asked Aug 2 '16 at 11:41
TimTim
9316
asked Aug 2 '16 at 11:41
TimTim
9316
asked Aug 2 '16 at 11:41
TimTim
9316
asked Aug 2 '16 at 11:41
TimTim
9316
asked Aug 2 '16 at 11:41
TimTim
9316
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
1
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
6
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
TimTim
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 |
1
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 ChongCurtis Chong
3311721
add a comment |
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 at 16:38
tehhowch
5,45341126
answered Jan 1 at 13:11
Rizwan HaqueRizwan Haque
14
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
6
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
TimTim
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 |
6
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
TimTim
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 |
6
6
6
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
TimTim
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
TimTim
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
565820
answered Aug 2 '16 at 11:59
TimTim
9316
answered Aug 2 '16 at 11:59
TimTim
9316
answered Aug 2 '16 at 11:59
TimTim
9316
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
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
@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
@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 |
1
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 ChongCurtis Chong
3311721
add a comment |
1
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 ChongCurtis Chong
3311721
add a comment |
1
1
1
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 ChongCurtis 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 ChongCurtis Chong
3311721
answered Aug 3 '18 at 16:34
Curtis ChongCurtis Chong
3311721
answered Aug 3 '18 at 16:34
Curtis ChongCurtis Chong
3311721
answered Aug 3 '18 at 16:34
Curtis ChongCurtis Chong
3311721
3311721
add a comment |
add a comment |
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 at 16:38
tehhowch
5,45341126
answered Jan 1 at 13:11
Rizwan HaqueRizwan Haque
14
for single cell used '"range": sheetName+"!B1",'
– Rizwan Haque
Jan 1 at 13:15
add a comment |
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 at 16:38
tehhowch
5,45341126
answered Jan 1 at 13:11
Rizwan HaqueRizwan Haque
14
for single cell used '"range": sheetName+"!B1",'
– Rizwan Haque
Jan 1 at 13:15
add a comment |
0
0
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 at 16:38
tehhowch
5,45341126
answered Jan 1 at 13:11
Rizwan HaqueRizwan Haque
14
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 HaqueRizwan Haque
14
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
5,45341126
answered Jan 1 at 13:11
Rizwan HaqueRizwan Haque
14
answered Jan 1 at 13:11
Rizwan HaqueRizwan Haque
14
answered Jan 1 at 13:11
Rizwan HaqueRizwan Haque
14
14
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
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
A good nights rest did me some good; I was able to get it working.
– Tim
Aug 2 '16 at 11:58