Google Sheets editing sheet based off of checkmark boxes
Sample Sheet
I have sheet2 setup with a complete list of work orders in column A. In column B I have a simple marker of 'TRUE' to signify the work is done. In sheet1 I have a filter to only display orders that are not marked 'TRUE' in column B from sheet2. Next to the list of orders in sheet1 are checkboxes. I would like a script that can read when a checkbox is marked, read what the item is next to the checkbox, mark the checkbox 'FALSE' to clear it, then mark the appropriate row in column B from sheet2 as 'TRUE' so the item leaves the list on sheet1.
I'll set the script up using an 'On edit' trigger, but I need help with creating the script that can do those function.
google-apps-script
asked Nov 14 '18 at 13:08
Joe PowlesJoe Powles
293
add a comment |
Sample Sheet
I have sheet2 setup with a complete list of work orders in column A. In column B I have a simple marker of 'TRUE' to signify the work is done. In sheet1 I have a filter to only display orders that are not marked 'TRUE' in column B from sheet2. Next to the list of orders in sheet1 are checkboxes. I would like a script that can read when a checkbox is marked, read what the item is next to the checkbox, mark the checkbox 'FALSE' to clear it, then mark the appropriate row in column B from sheet2 as 'TRUE' so the item leaves the list on sheet1.
I'll set the script up using an 'On edit' trigger, but I need help with creating the script that can do those function.
google-apps-script
asked Nov 14 '18 at 13:08
Joe PowlesJoe Powles
293
1
Post your code.
– TheMaster
Nov 14 '18 at 16:35
I honestly don't know where to start besides loading the sheet. var ss = SpreadsheetApp.getActive();
– Joe Powles
Nov 14 '18 at 19:14
To get started, you can try checking out the examples written in the guides. There are examples on how to read and write data to Sheets. For further guides, you can check out the Sheets documentation.
– Jacque
Nov 15 '18 at 7:18
add a comment |
Sample Sheet
I have sheet2 setup with a complete list of work orders in column A. In column B I have a simple marker of 'TRUE' to signify the work is done. In sheet1 I have a filter to only display orders that are not marked 'TRUE' in column B from sheet2. Next to the list of orders in sheet1 are checkboxes. I would like a script that can read when a checkbox is marked, read what the item is next to the checkbox, mark the checkbox 'FALSE' to clear it, then mark the appropriate row in column B from sheet2 as 'TRUE' so the item leaves the list on sheet1.
I'll set the script up using an 'On edit' trigger, but I need help with creating the script that can do those function.
google-apps-script
asked Nov 14 '18 at 13:08
Joe PowlesJoe Powles
293
Sample Sheet
I have sheet2 setup with a complete list of work orders in column A. In column B I have a simple marker of 'TRUE' to signify the work is done. In sheet1 I have a filter to only display orders that are not marked 'TRUE' in column B from sheet2. Next to the list of orders in sheet1 are checkboxes. I would like a script that can read when a checkbox is marked, read what the item is next to the checkbox, mark the checkbox 'FALSE' to clear it, then mark the appropriate row in column B from sheet2 as 'TRUE' so the item leaves the list on sheet1.
I'll set the script up using an 'On edit' trigger, but I need help with creating the script that can do those function.
google-apps-script
google-apps-script
asked Nov 14 '18 at 13:08
Joe PowlesJoe Powles
293
asked Nov 14 '18 at 13:08
Joe PowlesJoe Powles
293
asked Nov 14 '18 at 13:08
Joe PowlesJoe Powles
293
asked Nov 14 '18 at 13:08
Joe PowlesJoe Powles
293
asked Nov 14 '18 at 13:08
Joe PowlesJoe Powles
293
293
1
Post your code.
– TheMaster
Nov 14 '18 at 16:35
I honestly don't know where to start besides loading the sheet. var ss = SpreadsheetApp.getActive();
– Joe Powles
Nov 14 '18 at 19:14
To get started, you can try checking out the examples written in the guides. There are examples on how to read and write data to Sheets. For further guides, you can check out the Sheets documentation.
– Jacque
Nov 15 '18 at 7:18
add a comment |
1
Post your code.
– TheMaster
Nov 14 '18 at 16:35
I honestly don't know where to start besides loading the sheet. var ss = SpreadsheetApp.getActive();
– Joe Powles
Nov 14 '18 at 19:14
To get started, you can try checking out the examples written in the guides. There are examples on how to read and write data to Sheets. For further guides, you can check out the Sheets documentation.
– Jacque
Nov 15 '18 at 7:18
1
1
Post your code.
– TheMaster
Nov 14 '18 at 16:35
Post your code.
– TheMaster
Nov 14 '18 at 16:35
I honestly don't know where to start besides loading the sheet. var ss = SpreadsheetApp.getActive();
– Joe Powles
Nov 14 '18 at 19:14
I honestly don't know where to start besides loading the sheet. var ss = SpreadsheetApp.getActive();
– Joe Powles
Nov 14 '18 at 19:14
To get started, you can try checking out the examples written in the guides. There are examples on how to read and write data to Sheets. For further guides, you can check out the Sheets documentation.
– Jacque
Nov 15 '18 at 7:18
To get started, you can try checking out the examples written in the guides. There are examples on how to read and write data to Sheets. For further guides, you can check out the Sheets documentation.
– Jacque
Nov 15 '18 at 7:18
add a comment |
1 Answer
1
active
oldest
votes
after much searching and self debugging I came up with this. Does this look ok? I'm going to be placing this in a spreadsheet with many thousands of entries. I don't want it to be horribly inefficient.
function MarkComplete() {
var ss = SpreadsheetApp.getActive(); //Loading Current spreadsheet
var Display = ss.getSheetByName("Display"); //Loading Display sheet
var Complete = ss.getSheetByName("Complete"); //Loading Complete sheet
var data1 = Display.getDataRange().getValues(); //Loading data from sheet 1
var data2 = Complete.getDataRange().getValues(); //Loading data from sheet 2
for(var i = 0; i<data1.length;i++){
Logger.log('List item : ' + data1[i][0]);
Logger.log('Checked : ' + data1[i][1]);
if(data1[i][1] == true){ //Looking for rows in Display that are checked.
Logger.log('TRUE : ' + data1[i][0]);
for(var n = 0; n<data2.length;n++){
if(data2[n][0] == data1[i][0]){ //Looking for rows in Complete that match checked rows in Display
Complete.getRange(n+1,2).setValue("=true"); //set row x column 2 to true in complete
Display.getRange(i+1,2).setValue("false"); //set row i column 2 to false in display
}
}
}
}
}
answered Nov 15 '18 at 15:32
Joe PowlesJoe Powles
293
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
});
}
});
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%2f53300984%2fgoogle-sheets-editing-sheet-based-off-of-checkmark-boxes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
after much searching and self debugging I came up with this. Does this look ok? I'm going to be placing this in a spreadsheet with many thousands of entries. I don't want it to be horribly inefficient.
function MarkComplete() {
var ss = SpreadsheetApp.getActive(); //Loading Current spreadsheet
var Display = ss.getSheetByName("Display"); //Loading Display sheet
var Complete = ss.getSheetByName("Complete"); //Loading Complete sheet
var data1 = Display.getDataRange().getValues(); //Loading data from sheet 1
var data2 = Complete.getDataRange().getValues(); //Loading data from sheet 2
for(var i = 0; i<data1.length;i++){
Logger.log('List item : ' + data1[i][0]);
Logger.log('Checked : ' + data1[i][1]);
if(data1[i][1] == true){ //Looking for rows in Display that are checked.
Logger.log('TRUE : ' + data1[i][0]);
for(var n = 0; n<data2.length;n++){
if(data2[n][0] == data1[i][0]){ //Looking for rows in Complete that match checked rows in Display
Complete.getRange(n+1,2).setValue("=true"); //set row x column 2 to true in complete
Display.getRange(i+1,2).setValue("false"); //set row i column 2 to false in display
}
}
}
}
}
answered Nov 15 '18 at 15:32
Joe PowlesJoe Powles
293
add a comment |
after much searching and self debugging I came up with this. Does this look ok? I'm going to be placing this in a spreadsheet with many thousands of entries. I don't want it to be horribly inefficient.
function MarkComplete() {
var ss = SpreadsheetApp.getActive(); //Loading Current spreadsheet
var Display = ss.getSheetByName("Display"); //Loading Display sheet
var Complete = ss.getSheetByName("Complete"); //Loading Complete sheet
var data1 = Display.getDataRange().getValues(); //Loading data from sheet 1
var data2 = Complete.getDataRange().getValues(); //Loading data from sheet 2
for(var i = 0; i<data1.length;i++){
Logger.log('List item : ' + data1[i][0]);
Logger.log('Checked : ' + data1[i][1]);
if(data1[i][1] == true){ //Looking for rows in Display that are checked.
Logger.log('TRUE : ' + data1[i][0]);
for(var n = 0; n<data2.length;n++){
if(data2[n][0] == data1[i][0]){ //Looking for rows in Complete that match checked rows in Display
Complete.getRange(n+1,2).setValue("=true"); //set row x column 2 to true in complete
Display.getRange(i+1,2).setValue("false"); //set row i column 2 to false in display
}
}
}
}
}
answered Nov 15 '18 at 15:32
Joe PowlesJoe Powles
293
add a comment |
after much searching and self debugging I came up with this. Does this look ok? I'm going to be placing this in a spreadsheet with many thousands of entries. I don't want it to be horribly inefficient.
function MarkComplete() {
var ss = SpreadsheetApp.getActive(); //Loading Current spreadsheet
var Display = ss.getSheetByName("Display"); //Loading Display sheet
var Complete = ss.getSheetByName("Complete"); //Loading Complete sheet
var data1 = Display.getDataRange().getValues(); //Loading data from sheet 1
var data2 = Complete.getDataRange().getValues(); //Loading data from sheet 2
for(var i = 0; i<data1.length;i++){
Logger.log('List item : ' + data1[i][0]);
Logger.log('Checked : ' + data1[i][1]);
if(data1[i][1] == true){ //Looking for rows in Display that are checked.
Logger.log('TRUE : ' + data1[i][0]);
for(var n = 0; n<data2.length;n++){
if(data2[n][0] == data1[i][0]){ //Looking for rows in Complete that match checked rows in Display
Complete.getRange(n+1,2).setValue("=true"); //set row x column 2 to true in complete
Display.getRange(i+1,2).setValue("false"); //set row i column 2 to false in display
}
}
}
}
}
answered Nov 15 '18 at 15:32
Joe PowlesJoe Powles
293
after much searching and self debugging I came up with this. Does this look ok? I'm going to be placing this in a spreadsheet with many thousands of entries. I don't want it to be horribly inefficient.
function MarkComplete() {
var ss = SpreadsheetApp.getActive(); //Loading Current spreadsheet
var Display = ss.getSheetByName("Display"); //Loading Display sheet
var Complete = ss.getSheetByName("Complete"); //Loading Complete sheet
var data1 = Display.getDataRange().getValues(); //Loading data from sheet 1
var data2 = Complete.getDataRange().getValues(); //Loading data from sheet 2
for(var i = 0; i<data1.length;i++){
Logger.log('List item : ' + data1[i][0]);
Logger.log('Checked : ' + data1[i][1]);
if(data1[i][1] == true){ //Looking for rows in Display that are checked.
Logger.log('TRUE : ' + data1[i][0]);
for(var n = 0; n<data2.length;n++){
if(data2[n][0] == data1[i][0]){ //Looking for rows in Complete that match checked rows in Display
Complete.getRange(n+1,2).setValue("=true"); //set row x column 2 to true in complete
Display.getRange(i+1,2).setValue("false"); //set row i column 2 to false in display
}
}
}
}
}
answered Nov 15 '18 at 15:32
Joe PowlesJoe Powles
293
answered Nov 15 '18 at 15:32
Joe PowlesJoe Powles
293
answered Nov 15 '18 at 15:32
Joe PowlesJoe Powles
293
answered Nov 15 '18 at 15:32
Joe PowlesJoe Powles
293
293
add a comment |
add a comment |
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.
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%2f53300984%2fgoogle-sheets-editing-sheet-based-off-of-checkmark-boxes%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
Post your code.
– TheMaster
Nov 14 '18 at 16:35
I honestly don't know where to start besides loading the sheet. var ss = SpreadsheetApp.getActive();
– Joe Powles
Nov 14 '18 at 19:14
To get started, you can try checking out the examples written in the guides. There are examples on how to read and write data to Sheets. For further guides, you can check out the Sheets documentation.
– Jacque
Nov 15 '18 at 7:18