0
204
ViewsApps Script: How to limit the scope of a FOR LOOP?
The script below runs whenever any cell in column E (so, array [4]) has been edited. And its only purpose is to add some additional information to the subsequent empty cells of the row that has been edited. But instead of just adding these information to the next cells that are empty in the same row, my script is re-setting the values of all subsequent cells, even if these subsequent cells already have each some values in them. Can somebody please tell me what I am doing wrong here and how to fix it? It´s obvious that I am doing something wrong in my IF-statement, but I honestly have no idea what that could be :(
Thank you so much in advance for your help and hints!!!
Here is the script:
function addAdditionalInfo() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('SHEET_ID');
var data = s.getDataRange().getValues();
var data_len = data.length;
for (var i = 0; i < data_len; i++) {
if (data[i][4] == "COMPLETED") {
s.getRange(i + 1, 6).setValue("name");
s.getRange(i + 1, 7).setValue("street");
s.getRange(i + 1, 8).setValue("street number");
s.getRange(i + 1, 9).setValue("zip code");
s.getRange(i + 1, 10).setValue("country");
s.getRange(i + 1, 11).setValue("maternal language");
s.getRange(i + 1, 12).setValue("second language");
s.getRange(i + 1, 13).setValue("-");
}
else if (data[i][4] == "APPROVAL") {
s.getRange(i + 1, 6).setValue("name of supervisor)");
s.getRange(i + 1, 7).setValue("email of supervisor");
s.getRange(i + 1, 8).setValue("tel of supervisor");
s.getRange(i + 1, 9).setValue("maternal language of supervisor");
s.getRange(i + 1, 10).setValue("second language of supervisor");
s.getRange(i + 1, 11).setValue("assistant of the supervisor");
s.getRange(i + 1, 12).setValue("status of the approval process");
s.getRange(i + 1, 13).setValue("approval due date");
}
}
}
And here is the trigger:
// The trigger is any manual change done in Column E, starting at row 3 of that column
function onEdit(e) {
if (
e.source.getSheetName() == "SHEET_ID" &&
e.range.columnStart == 5 &&
e.range.columnEnd == 5 &&
e.range.rowStart >= 3 &&
e.range.rowEnd <= 3000
) {
addAdditionalInfo()
}
}
1 answers
Answer question0
Adding data to just line that you are on will limit the scope
Trying to loop through the entire sheet is a bad idea since this script will max out after 30 seconds.
In my testing I used a validation in column 5 using the list COMPLETED,APPROVAL
function onEdit(e) {
e.source.toast('entry')
const sh = e.range.getSheet();
if (sh.getName() == 'Sheet0' && e.range.columnStart == 5 && e.range.rowStart > 2) {
e.source.toast('completed');
if (e.value == "COMPLETED") {
sh.getRange(e.range.rowStart, 6, 1, 8).setValues([["name", "street", "street number", "zip code", "country", "maternal language", "second language", "-"]]);
}
if (e.value == "APPROVAL") {
e.source.toast('approval')
sh.getRange(e.range.rowStart, 6, 1, 8).setValues([["name of supervisor", "email of supervisor", "tel of supervisor", "maternal language of supervisor", "second language of supervisor", "assistant of the supervisor", "status of the approval process", "approval due date"]]);
}
}
}
Sheet0:
