For Loop to Delete Potentially Multiple Columns in Google Apps Script

Question

I have a Google Sheet where the user can select one of several options in Column A. There are 7 cells within this column where they can select an option. If the user chooses "Omit Day", I need the corresponding column to delete (this gets transferred to a different sheet). I have the following code:

//Delete all cells set to 'Omit Day'.
  var omit_vals = [];
  var impact_locs = ['A22', 'A23', 'A24','A25', 'A26', 'A27', 'A28'];
  var hazard_briefing_placement = ['B13', 'C13', 'D13', 'E13', 'F13', 'G13', 'H13'];
  var hazard_date_briefing_placement = ['B12', 'C12', 'D12', 'E12', 'F12', 'G12', 'H12'];
    for (var zx = 0; zx < 7; zx++){
     var range_omit = String(SpreadsheetApp.getActiveSheet().getRange('main_gen!'+impact_locs[zx]).getValue());
      if (range_omit == 'Omit Day'){
      omit_vals = zx;
        var range_omit_del = body.getRange(hazard_briefing_placement[omit_vals]);
        range_omit_del.deleteCells(SpreadsheetApp.Dimension.COLUMNS);
        var range_omit_del2 = body.getRange(hazard_date_briefing_placement[omit_vals]);
        range_omit_del2.deleteCells(SpreadsheetApp.Dimension.COLUMNS);
        Logger.log(omit_vals);
    }
  }

Omit_vals properly stores the correct value of the column I would like to delete. So with the way the code is currently written, the first time it loops through it deletes the correct column. However, there is now one less column for when it loops through the second time, so when there is a second (or third, or fourth, etc.) column it needs to delete, it now is off by one column due to there being one less to begin with. I am not sure how I can account for this. Any idea of what I need to correct? I am pretty sure it is within the hazard_briefing_placement[omit_vals] line, but I am not sure how to fix this. Thanks!

Although I'm not sure whether I could correctly understand your issue because I couldn't test it, how about modifying from for (var zx = 0; zx < 7; zx++){ to for (var zx = 6; zx >= 0; zx--){? If this was not the direct solution, I apologize. — Tanaike
– Tanaike, Commented Nov 21, 2019 at 0:56
That nearly works. For some reason it keeps deleting one extra column at the left end. Aside from that hiccup, it deletes the rest of the columns as wanted. — hunter21188
– hunter21188, Commented Nov 21, 2019 at 1:14
Thank you for replying. I apologize that my comment didn't resolve your issue. In the current stage, I think that it is required to confirm your current issue. In order to test the script, can you provide a sample Spreadsheet for replicating the current issue? Of course, please remove your personal information. By this, I would like to confirm it. — Tanaike
– Tanaike, Commented Nov 21, 2019 at 1:25
Ok, here is a version you can test: docs.google.com/spreadsheets/d/… — hunter21188
– hunter21188, Commented Nov 21, 2019 at 1:34
@Tanaike your original solution actually did work perfectly. The reason it was deleting an extra cell is because of a line of code elsewhere. I removed that line and it works exactly as it should. Thanks! If you post that as an answer I will accept it. Thanks again. — hunter21188
– hunter21188, Commented Nov 22, 2019 at 0:04

Tanaike · Accepted Answer · 2019-11-22 00:23:23Z

3

The modification point of your script is as follows.

From:

for (var zx = 0; zx < 7; zx++){

To:

for (var zx = 6; zx >= 0; zx--){

When the column is deleted with ascending, 1st loop work. But at 2nd loop, the script is not correctly worked because the column number is changed by deleting the column at the 1st loop. This is the reason of your issue.
When the column is delete with descending, the script is correctly worked because the column number is not changed.
Of course, even when the column is deleted with ascending, when the column number is updated every loop, the script is correctly worked. But I think that the descending loop becomes simpler script.

answered Nov 22, 2019 at 0:23

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

For Loop to Delete Potentially Multiple Columns in Google Apps Script

1 Answer 1

From:

To:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From:

To:

Comments

Your Answer

Sign up or log in

Post as a guest

Related