CODEWITHSUNDEEP
javascriptfor-loopgoogle-apps-scriptgoogle-sheets
afrologicinsect
afrologicinsect

Reputation: 171

Google Apps Script: Loop through a list

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.

Upvotes: 1

Views: 2091

Answers (2)

Yuri Khristich
Yuri Khristich

Reputation: 14537

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.

Upvotes: 1

Umair Mohammad
Umair Mohammad

Reputation: 4635

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

Upvotes: 3

Related Questions