Google Apps Script: Loop through a list

Question

I have a deleteEachRow function that loops through a sheet and delete Rows that have a particular Column Value.

This works fine and was hoping to modify it in such a way that it loops through a multile sheets in the work-book and also delete rows based on multiple Column Values.

The deleteRow() script

//GLOBALS
 
var SS = SpreadsheetApp.openById("sheetID"); 
var SHEET = SS.getSheetByName("Sheet1");
var RANGE = SHEET.getDataRange();
 
 
var DELETE_VAL = "abc";
var COL_TO_SEARCH = 4; // The column to search for the DELETE_VAL (Zero is first)
 
  
function deleteEachRow(){
  
  var rangeVals = RANGE.getValues();
  
  //Reverse the 'for' loop.
  for(var i = rangeVals.length-1; i >= 0; i--){
    if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL){
      
      SHEET.deleteRow(i+1); 
    };
  };
};

What I have tried..

var SHEET = SS.getSheetByName(["Sheet1", "Sheet2"]);
 
var DELETE_VAL = ["abc","DEF"];

function deleteEachRow(){
  
  var rangeVals = RANGE.getValues();
  
  //Reverse the 'for' loop.
  for(var i = rangeVals.length-1; i >= 0; i--){
    for(var i=0; size = DELETE_VAL.length; i < size; i++){
        if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL[i]){
         for(var i=0; size = SHEET.length; i < size; i++){
             SHEET[i].deleteRow(i+1); 
      };
     };
    };
  };
};

Which completes executing from my logs, but does not actually work. I may have murdered some logic here, please pardon me, I am new to .gs/.js.

Thanks for your anticipated response.

Umair Mohammad · Accepted Answer · 2021-06-14 10:54:15Z

3

Issue : You're passing array to getSheetByName, whereas as per documentation it accepts String only. i.e. Name of the single sheet you want to fetch.

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetbynamename

So you can modify your function to take sheet name as input and then delete rows in that sheet. Then call your function with desired sheet names. Something like this:

var spreadSheet = SpreadsheetApp.openById("sheetID");  
 
var DELETE_VAL = "abc";
var COL_TO_SEARCH = 4; // The column to search for the DELETE_VAL (Zero is first)
 
function deleteEachRow(sheetName){
  var SHEET = spreadSheet.getSheetByName(sheetName);
  var RANGE = SHEET.getDataRange();
  var rangeVals = RANGE.getValues();
  
  // existing logic
};

// Invoke deleteEachRow() for each sheet you want to delete the rows
["Sheet1", "Sheet2"].forEach((sheetName) => deleteEachRow(sheetName));

answered Jun 14, 2021 at 10:54

Umair Mohammad

4,6432 gold badges22 silver badges35 bronze badges

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

3 Comments

afrologicinsect Over a year ago

Hi @Umair Mohammed This worked quite well, Although it threw an error. Please is there a way modify this to pass a list of items to DELETE_VAL?

Umair Mohammad Over a year ago

Yes. you can make DELETE_VAL to be an array and loop through it using forEach (etc). Optimised version would be to use map/dictionary instead of array - that way you won't need to loop through the array. Please try it out and if you're stuck feel free to open new question stackoverflow.com/help/how-to-ask

afrologicinsect Over a year ago

Hi @Umair Mohammed, thanks for this really. What you suggested did work, and this has really optimised the task, however I have tried as you said to implement a for-loop through an array for deleting the multiple items. I have not succeeded, I have post the challenge here, hope you could check it out. Thanks again

Yuri Khristich · Accepted Answer · 2021-06-14 11:20:46Z

1

Umair is right, there was a simply error in the first line. But I'd want to add that the sheet.deleteRow(row) is not the best practice in case if there are many rows to delete. This command is quite time consuming.

If you have more than dozen rows to delete it's better to grab all data from a sheet (or range) var data = range.getValues(), clear the sheet (or the range), to process the array inside the script and refill the sheet back with new data new_range.setValues(array). It will work much faster.

answered Jun 14, 2021 at 11:20

Yuri Khristich

14.6k3 gold badges11 silver badges25 bronze badges

1 Comment

afrologicinsect Over a year ago

Thanks @Yuri Khristich, my knowledge here is quite limited and really wished this could understood thoroughly. However I have posted a related question here, could you help, check it out. I can not thank you enough.

Collectives™ on Stack Overflow

Google Apps Script: Loop through a list

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related