Google App Script sheet that moves row to correct sheet
$begingroup$
I've got this code to loop through each cell in Column A and check the cell value. If the value matches the if criteria it moves the entire row to the correct Sheet and then replaces the Column A cell with an X. This makes a call to the server for every cell and I'm curious if there is a way for me to optimize it similar to what Google has documented as Best Practices. The Cache service is confusing me.
Here is a copy of the spreadsheet I'm working on. The code has a menu execution under Move Row.
function moveToQA() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var columnNumberToWatch = 1;
var targetSheet = ss.getSheetByName("QA 👌");
for( var i = 3; i >= 3; i++ ) {
var cell = sheet.getRange(i,1);
var cellValue = cell.getValue();
if (cellValue == "Captures Sent 📩" || cellValue == "QA In Progress 👌" || cellValue == "Recapture Completed 📷") {
var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
sheet.getRange(cell.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
cell.setValue("X");
}
else if (cellValue != "") {
continue;
}
else if (cellValue == "" || cell == lastRow) {
break;
}
}
}
google-apps-script
edited 30 mins ago
Jamal♦
30.3k11119227
asked Feb 1 at 14:09
Shane SullivanShane Sullivan
61
$endgroup$
add a comment |
$begingroup$
I've got this code to loop through each cell in Column A and check the cell value. If the value matches the if criteria it moves the entire row to the correct Sheet and then replaces the Column A cell with an X. This makes a call to the server for every cell and I'm curious if there is a way for me to optimize it similar to what Google has documented as Best Practices. The Cache service is confusing me.
Here is a copy of the spreadsheet I'm working on. The code has a menu execution under Move Row.
function moveToQA() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var columnNumberToWatch = 1;
var targetSheet = ss.getSheetByName("QA 👌");
for( var i = 3; i >= 3; i++ ) {
var cell = sheet.getRange(i,1);
var cellValue = cell.getValue();
if (cellValue == "Captures Sent 📩" || cellValue == "QA In Progress 👌" || cellValue == "Recapture Completed 📷") {
var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
sheet.getRange(cell.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
cell.setValue("X");
}
else if (cellValue != "") {
continue;
}
else if (cellValue == "" || cell == lastRow) {
break;
}
}
}
google-apps-script
edited 30 mins ago
Jamal♦
30.3k11119227
asked Feb 1 at 14:09
Shane SullivanShane Sullivan
61
$endgroup$
add a comment |
$begingroup$
I've got this code to loop through each cell in Column A and check the cell value. If the value matches the if criteria it moves the entire row to the correct Sheet and then replaces the Column A cell with an X. This makes a call to the server for every cell and I'm curious if there is a way for me to optimize it similar to what Google has documented as Best Practices. The Cache service is confusing me.
Here is a copy of the spreadsheet I'm working on. The code has a menu execution under Move Row.
function moveToQA() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var columnNumberToWatch = 1;
var targetSheet = ss.getSheetByName("QA 👌");
for( var i = 3; i >= 3; i++ ) {
var cell = sheet.getRange(i,1);
var cellValue = cell.getValue();
if (cellValue == "Captures Sent 📩" || cellValue == "QA In Progress 👌" || cellValue == "Recapture Completed 📷") {
var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
sheet.getRange(cell.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
cell.setValue("X");
}
else if (cellValue != "") {
continue;
}
else if (cellValue == "" || cell == lastRow) {
break;
}
}
}
google-apps-script
edited 30 mins ago
Jamal♦
30.3k11119227
asked Feb 1 at 14:09
Shane SullivanShane Sullivan
61
$endgroup$
I've got this code to loop through each cell in Column A and check the cell value. If the value matches the if criteria it moves the entire row to the correct Sheet and then replaces the Column A cell with an X. This makes a call to the server for every cell and I'm curious if there is a way for me to optimize it similar to what Google has documented as Best Practices. The Cache service is confusing me.
Here is a copy of the spreadsheet I'm working on. The code has a menu execution under Move Row.
function moveToQA() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var columnNumberToWatch = 1;
var targetSheet = ss.getSheetByName("QA 👌");
for( var i = 3; i >= 3; i++ ) {
var cell = sheet.getRange(i,1);
var cellValue = cell.getValue();
if (cellValue == "Captures Sent 📩" || cellValue == "QA In Progress 👌" || cellValue == "Recapture Completed 📷") {
var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
sheet.getRange(cell.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
cell.setValue("X");
}
else if (cellValue != "") {
continue;
}
else if (cellValue == "" || cell == lastRow) {
break;
}
}
}
google-apps-script
google-apps-script
edited 30 mins ago
Jamal♦
30.3k11119227
asked Feb 1 at 14:09
Shane SullivanShane Sullivan
61
edited 30 mins ago
Jamal♦
30.3k11119227
asked Feb 1 at 14:09
Shane SullivanShane Sullivan
61
edited 30 mins ago
Jamal♦
30.3k11119227
edited 30 mins ago
Jamal♦
30.3k11119227
edited 30 mins ago
Jamal♦
30.3k11119227
30.3k11119227
asked Feb 1 at 14:09
Shane SullivanShane Sullivan
61
asked Feb 1 at 14:09
Shane SullivanShane Sullivan
61
asked Feb 1 at 14:09
Shane SullivanShane Sullivan
61
61
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
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: "196"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fcodereview.stackexchange.com%2fquestions%2f212691%2fgoogle-app-script-sheet-that-moves-row-to-correct-sheet%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Code Review Stack Exchange!
- 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.
Use MathJax to format equations. MathJax reference.
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%2fcodereview.stackexchange.com%2fquestions%2f212691%2fgoogle-app-script-sheet-that-moves-row-to-correct-sheet%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
