Updating a cell in google sheets when another cell is updated
up vote
2
down vote
favorite
I have a Googlesheet that is linked to a form. I am looking to get the column named "MSP" to update to say "SRM" when the column "Team Escalation is sat with" equals Request. I can create this if statement very easily in the Googlesheet Cell, however. Because the form is linked to the sheet, even though "team escalation is sat with" is a column that is not populated by the sheet, it is overwritten when the row is populated with data from the sheet and turns into a blank cell with no IF statement appended anymore. Due to this I am trying to carry this out in script. I have the below but I am clearly getting something wrong. Any help would be great
function onEdit(e) {
var sheet = e.source.getActiveSheet();
if (sheet.getName() == "Form responses 1") {
//"Form responses 1" is the name of the sheet
var actRng = sheet.getActiveRange();
var editColumn = actRng.getColumn();
var rowIndex = actRng.getRowIndex();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
var mspCol = headers[0].indexOf("MSP");
var nswCol = headers[0].indexOf("Escalation is now sat with ");
if (mspCol > 0 && rowIndex > 1 && editColumn == nswCol && nswCol=="Request") {
sheet
.getRange(rowIndex, mspCol)
.setValue(mspCol= "SRM");
}}}
google-apps-script google-sheets google-form asked Nov 22 at 14:37
RWLY
111
add a comment |
up vote
2
down vote
favorite
I have a Googlesheet that is linked to a form. I am looking to get the column named "MSP" to update to say "SRM" when the column "Team Escalation is sat with" equals Request. I can create this if statement very easily in the Googlesheet Cell, however. Because the form is linked to the sheet, even though "team escalation is sat with" is a column that is not populated by the sheet, it is overwritten when the row is populated with data from the sheet and turns into a blank cell with no IF statement appended anymore. Due to this I am trying to carry this out in script. I have the below but I am clearly getting something wrong. Any help would be great
function onEdit(e) {
var sheet = e.source.getActiveSheet();
if (sheet.getName() == "Form responses 1") {
//"Form responses 1" is the name of the sheet
var actRng = sheet.getActiveRange();
var editColumn = actRng.getColumn();
var rowIndex = actRng.getRowIndex();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
var mspCol = headers[0].indexOf("MSP");
var nswCol = headers[0].indexOf("Escalation is now sat with ");
if (mspCol > 0 && rowIndex > 1 && editColumn == nswCol && nswCol=="Request") {
sheet
.getRange(rowIndex, mspCol)
.setValue(mspCol= "SRM");
}}}
asked Nov 22 at 14:37
RWLY
111
What's not working? Any error messages you getting or just not getting the expected result?
– New_2_Code
Nov 27 at 10:57
Hi @New_2_Code , unfortunately nothing at all :( no error messages etc. very strange. unsure what im missing. Have triple checked the variable names and they are all correct. Have even checked pre and post update of the row by the form which also makes no difference
– RWLY
Nov 28 at 11:37
I would then investigate your second if statement. By the sounds of it not all criteria is being met. Be sure to use the debugging tool to make sure the values of the variables are what you expect them to be. Also make sure you are using the correct comparison operators. It's important to remember the code will only do what you tell it to do.
– New_2_Code
Nov 28 at 12:28
@RWLY Have you resolved your problem, or are you still in need of help?
– Tedinoz
Dec 11 at 2:09
Hi @tedinoz thanks for getting back to me, I have managed to resolve it. I will close the question now
– RWLY
Dec 13 at 15:54
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I have a Googlesheet that is linked to a form. I am looking to get the column named "MSP" to update to say "SRM" when the column "Team Escalation is sat with" equals Request. I can create this if statement very easily in the Googlesheet Cell, however. Because the form is linked to the sheet, even though "team escalation is sat with" is a column that is not populated by the sheet, it is overwritten when the row is populated with data from the sheet and turns into a blank cell with no IF statement appended anymore. Due to this I am trying to carry this out in script. I have the below but I am clearly getting something wrong. Any help would be great
function onEdit(e) {
var sheet = e.source.getActiveSheet();
if (sheet.getName() == "Form responses 1") {
//"Form responses 1" is the name of the sheet
var actRng = sheet.getActiveRange();
var editColumn = actRng.getColumn();
var rowIndex = actRng.getRowIndex();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
var mspCol = headers[0].indexOf("MSP");
var nswCol = headers[0].indexOf("Escalation is now sat with ");
if (mspCol > 0 && rowIndex > 1 && editColumn == nswCol && nswCol=="Request") {
sheet
.getRange(rowIndex, mspCol)
.setValue(mspCol= "SRM");
}}}
asked Nov 22 at 14:37
RWLY
111
I have a Googlesheet that is linked to a form. I am looking to get the column named "MSP" to update to say "SRM" when the column "Team Escalation is sat with" equals Request. I can create this if statement very easily in the Googlesheet Cell, however. Because the form is linked to the sheet, even though "team escalation is sat with" is a column that is not populated by the sheet, it is overwritten when the row is populated with data from the sheet and turns into a blank cell with no IF statement appended anymore. Due to this I am trying to carry this out in script. I have the below but I am clearly getting something wrong. Any help would be great
function onEdit(e) {
var sheet = e.source.getActiveSheet();
if (sheet.getName() == "Form responses 1") {
//"Form responses 1" is the name of the sheet
var actRng = sheet.getActiveRange();
var editColumn = actRng.getColumn();
var rowIndex = actRng.getRowIndex();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
var mspCol = headers[0].indexOf("MSP");
var nswCol = headers[0].indexOf("Escalation is now sat with ");
if (mspCol > 0 && rowIndex > 1 && editColumn == nswCol && nswCol=="Request") {
sheet
.getRange(rowIndex, mspCol)
.setValue(mspCol= "SRM");
}}}
asked Nov 22 at 14:37
RWLY
111
asked Nov 22 at 14:37
RWLY
111
asked Nov 22 at 14:37
RWLY
111
asked Nov 22 at 14:37
RWLY
111
asked Nov 22 at 14:37
RWLY
111
111
What's not working? Any error messages you getting or just not getting the expected result?
– New_2_Code
Nov 27 at 10:57
Hi @New_2_Code , unfortunately nothing at all :( no error messages etc. very strange. unsure what im missing. Have triple checked the variable names and they are all correct. Have even checked pre and post update of the row by the form which also makes no difference
– RWLY
Nov 28 at 11:37
I would then investigate your second if statement. By the sounds of it not all criteria is being met. Be sure to use the debugging tool to make sure the values of the variables are what you expect them to be. Also make sure you are using the correct comparison operators. It's important to remember the code will only do what you tell it to do.
– New_2_Code
Nov 28 at 12:28
@RWLY Have you resolved your problem, or are you still in need of help?
– Tedinoz
Dec 11 at 2:09
Hi @tedinoz thanks for getting back to me, I have managed to resolve it. I will close the question now
– RWLY
Dec 13 at 15:54
add a comment |
What's not working? Any error messages you getting or just not getting the expected result?
– New_2_Code
Nov 27 at 10:57
Hi @New_2_Code , unfortunately nothing at all :( no error messages etc. very strange. unsure what im missing. Have triple checked the variable names and they are all correct. Have even checked pre and post update of the row by the form which also makes no difference
– RWLY
Nov 28 at 11:37
I would then investigate your second if statement. By the sounds of it not all criteria is being met. Be sure to use the debugging tool to make sure the values of the variables are what you expect them to be. Also make sure you are using the correct comparison operators. It's important to remember the code will only do what you tell it to do.
– New_2_Code
Nov 28 at 12:28
@RWLY Have you resolved your problem, or are you still in need of help?
– Tedinoz
Dec 11 at 2:09
Hi @tedinoz thanks for getting back to me, I have managed to resolve it. I will close the question now
– RWLY
Dec 13 at 15:54
What's not working? Any error messages you getting or just not getting the expected result?
– New_2_Code
Nov 27 at 10:57
What's not working? Any error messages you getting or just not getting the expected result?
– New_2_Code
Nov 27 at 10:57
Hi @New_2_Code , unfortunately nothing at all :( no error messages etc. very strange. unsure what im missing. Have triple checked the variable names and they are all correct. Have even checked pre and post update of the row by the form which also makes no difference
– RWLY
Nov 28 at 11:37
Hi @New_2_Code , unfortunately nothing at all :( no error messages etc. very strange. unsure what im missing. Have triple checked the variable names and they are all correct. Have even checked pre and post update of the row by the form which also makes no difference
– RWLY
Nov 28 at 11:37
I would then investigate your second if statement. By the sounds of it not all criteria is being met. Be sure to use the debugging tool to make sure the values of the variables are what you expect them to be. Also make sure you are using the correct comparison operators. It's important to remember the code will only do what you tell it to do.
– New_2_Code
Nov 28 at 12:28
I would then investigate your second if statement. By the sounds of it not all criteria is being met. Be sure to use the debugging tool to make sure the values of the variables are what you expect them to be. Also make sure you are using the correct comparison operators. It's important to remember the code will only do what you tell it to do.
– New_2_Code
Nov 28 at 12:28
@RWLY Have you resolved your problem, or are you still in need of help?
– Tedinoz
Dec 11 at 2:09
@RWLY Have you resolved your problem, or are you still in need of help?
– Tedinoz
Dec 11 at 2:09
Hi @tedinoz thanks for getting back to me, I have managed to resolve it. I will close the question now
– RWLY
Dec 13 at 15:54
Hi @tedinoz thanks for getting back to me, I have managed to resolve it. I will close the question now
– RWLY
Dec 13 at 15:54
add a comment |
active
oldest
votes
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',
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
});
}
});
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%2f53433262%2fupdating-a-cell-in-google-sheets-when-another-cell-is-updated%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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%2f53433262%2fupdating-a-cell-in-google-sheets-when-another-cell-is-updated%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
What's not working? Any error messages you getting or just not getting the expected result?
– New_2_Code
Nov 27 at 10:57
Hi @New_2_Code , unfortunately nothing at all :( no error messages etc. very strange. unsure what im missing. Have triple checked the variable names and they are all correct. Have even checked pre and post update of the row by the form which also makes no difference
– RWLY
Nov 28 at 11:37
I would then investigate your second if statement. By the sounds of it not all criteria is being met. Be sure to use the debugging tool to make sure the values of the variables are what you expect them to be. Also make sure you are using the correct comparison operators. It's important to remember the code will only do what you tell it to do.
– New_2_Code
Nov 28 at 12:28
@RWLY Have you resolved your problem, or are you still in need of help?
– Tedinoz
Dec 11 at 2:09
Hi @tedinoz thanks for getting back to me, I have managed to resolve it. I will close the question now
– RWLY
Dec 13 at 15:54