Arrays

Challenge Looping through an array to delete rows in Google Apps Script

admin
Welcome to Programming Tutorial official website. Today - we are going to cover how to solve / find the solution of this error Challenge Looping through an array to delete rows in Google Apps Script on this date .

The following function deletes rows in a Google sheet based on a specific value/item, I was hoping to modify this to take an array of items to delete.

Original function

//GLOBALS
 
 
var spreadSheet = SpreadsheetApp.openById("SHEET ID"); 
 
 
var DELETE_VAL = ["Item_to_delete 1", "Item_to_delete 2", "Item_to_delete 3"]
var COL_TO_SEARCH = 5; // 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();
  
  //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); 
    };
  };
};

// Invoke deleteEachRow() for each sheet you want to delete the rows
["SHEET 1", "SHEET 2", "SHEET N"].forEach((sheetName) => deleteEachRow(sheetName))

I have tried passing a for-loop that goes through each item but it throws an error:

//GLOBALS
 
var spreadSheet = SpreadsheetApp.openById("SHEET ID"); 
 
 
var DELETE_VAL = ["Item_to_delete 1", "Item_to_delete 2", "Item_to_delete 3"]
var COL_TO_SEARCH = 7; // 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();
  
  //Reverse the 'for' loop.
  for(var i = rangeVals.length-1; i >= 0; i--){
    for(var i = DELETE_VAL.length; i >=0; i++){
        if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL[i]){
      
           SHEET.deleteRow(i+1); 
      };
    };
  };
};

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

TypeError: Cannot read property ‘7’ of undefined

Meaning it is no longer reading the rangeVals variable.

I have also tried

.
.
.
// Invoke deleteEachRow() for each sheet you want to delete the rows
for(var i = DELETE_VAL.length; i >=0; i++){
   ["SHEET 1", "SHEET 2", "SHEET N"].forEach((sheetName) => deleteEachRow(sheetName));
};

But this just executes indefinitely until Time out.

I know i’ve definitely flawed some logic, but i’m almost there, please help. My knowledge of .js/.gs is minimal.

What I have tried again.

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

Answer

Issues:

  • If you have two nested for loops, make sure both counter variables are not the same (i).
  • j is used as an index of DELETE_VAL. This doesn’t correspond to the sheets rows you want to delete (SHEET.deleteRow(j+1);). That should be i instead (SHEET.deleteRow(i+1);).
  • Also, instead of having an inner for loop, I’d suggest using includes in order to check whether the array DELETE_VAL includes the corresponding cell value. This way, there’s no need to declare j, which has been the cause of this confusion.

Code snippet:

function deleteEachRow(sheetName){
  var SHEET = spreadSheet.getSheetByName(sheetName);
  var RANGE = SHEET.getDataRange();
  var rangeVals = RANGE.getValues();
  for(var i = rangeVals.length-1; i >= 0; i--){
    if (DELETE_VAL.includes(rangeVals[i][COL_TO_SEARCH])) {
      SHEET.deleteRow(i+1);
    };
  };
};