I have this script to add importrange formulas:
function setImportRangeFromList1() {
const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List");
const rows = ss.getRange('A2:D').getValues().filter(r=> r[1])
rows.map(row => SpreadsheetApp.openById(row[0]).getSheetByName(row[1]).getRange(row[2]).setFormula(row[3]))
}
and here the spreadsheet: https://docs.google.com/spreadsheets/d/1GeVSs3zfuxNYzGybwpTuGGNcwV7U--8VksUHzc5f9p4/edit#gid=0
What i'm looking for is adding word like "done" or "added" to the completed rows, and i want the script not to do these rows again when i run at the next time. I hope anyone help me with this.
In your situation, how about the following modification?
function setImportRangeFromList1() {
const check = "done";
const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List");
const rows = ss.getRange('A2:E').getValues();
const ranges = rows.map((row, i) => {
if (row[1] && row[4] != check) {
SpreadsheetApp.openById(row[0]).getSheetByName(row[1]).getRange(row[2]).setFormula(row[3]);
return `E${i + 2}`;
}
return "";
}).filter(String);
if (ranges.length == 0) return;
ss.getRangeList(ranges).setValue(check);
}
done
, SpreadsheetApp.openById(row[0]).getSheetByName(row[1]).getRange(row[2]).setFormula(row[3])
is run, and done
is put to the column "E". By this, when the script is run 2nd time, the rows with done
in the column "E" are skipped.added
instead of done
, please modify const check = "done";
.