How do I have a script run through all rows with c

2019-08-24 23:33发布

问题:

The script I am using loops through a spreadsheet and deletes items in rows where there is a checkmark.

I need it to have a variation on certain rows. In this example, I also need it to delete contents in column A for rows 123-137 if the checkbox in F is checked.

This script is amazing script, and was helped tremendously by Tanaike

If Checkbox is checked, use a script to clear specified cells in row and clear checkbox after script is run

I've tried changing the script below to include:

return ar.concat(["B" + (i + 1) + ":D" + (i + 1), "F" + (i + 1), "A123:A137"]);

And lots of other tweaks. I don't fully understand what is going on in this script.

function deleteRowContents (col){ // col is the index of the column to check for checkbox being true
  var col = 6; // If the column "F" is 6, please set 6.
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet4"); // Modified
  var data = sh.getDataRange().getValues();

  // Below script was modified.
  var deleteRanges = data.reduce(function(ar, e, i) {
    if (e[col - 1] === true) { // Modified
      return ar.concat(["B" + (i + 1) + ":D" + (i + 1), "F" + (i + 1)]);
    }
    return ar;
  }, []);
  if (deleteRanges.length > 0) { // or if (deleteRanges.length) { // Added
    sh.getRangeList(deleteRanges).clearContent();
  }
}

EXPECTED RESULTS: For specified rows 123-137, also clearContent from Column A if checkbox in F is checked.

Actual results: Script errors, adding exponential number of rows, etc.

回答1:

  • When the checkbox put at the column "F" was checked, you want to delete the values of A123:A137.

If my understanding is correct, how about this modification?

From:

if (deleteRanges.length > 0) { // or if (deleteRanges.length) { // Added
  sh.getRangeList(deleteRanges).clearContent();
}

To:

if (deleteRanges.length > 0) {
  deleteRanges.push("A123:A137"); // Added
  sh.getRangeList(deleteRanges).clearContent();
}

Note:

  • About Actual results: Script errors, adding exponential number of rows, etc., I couldn't understand about your situation. So if above modification was not useful for your situation, can you provide a sample Spreadsheet for replicating the error? By this, I would like to modify it.

Edit:

  • When the checkbox put at the column "F" was checked, you want to delete the values of A123:A137.
    • From I also need it to delete contents in column A for rows 123-137 if the checkbox in F is checked. in your question, I understood like above modification.
    • But in your comment, you say It should only delete contents in A123 if F123 was checked, for example..

From above, I understood like below.

  • When the checked rows are from 123 to 137, you want to delete the value of the column "A" corresponding to the row.

If my understanding is correct, how about this modification?

From:

return ar.concat(["B" + (i + 1) + ":D" + (i + 1), "F" + (i + 1)]);

To:

if (i + 1 >= 123 && i + 1 <= 137) {
  return ar.concat(["B" + (i + 1) + ":D" + (i + 1), "F" + (i + 1), "A" + (i + 1)]);
}
return ar.concat(["B" + (i + 1) + ":D" + (i + 1), "F" + (i + 1)]);