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.

CodePudding user response：

In your situation, how about the following modification?

Modified script:

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);
}

• In this modification, when the the column "E" is checked, when the cell value is not 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.

Note:

• If you want to use added instead of done, please modify const check = "done";.

References:

• map()
• getRangeList(a1Notations)