CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheets
Allison Bucklew
Allison Bucklew

Reputation: 13

Google Scripts how to delete extra rows from an array

I am working on a google sheets template that will have some roster maintenance built in. When rosters are updated on the main "roster" tab, I would like for all the other tabs in the sheet to check student ID #s against the updated roster tab. In the code, an example sheet is "anet" sheets the sheets. I am using indexOf and a for loop to check each value in the "anet" sheet against the IDs in the "roster" sheet. If an ID# has been removed from the "roster" sheet, I would like that row to be deleted in the "anet" sheet.

When I run the script right now, some of the rows are deleted, but not all of them. The list of IDs begins in A3 on the "roster" tab, and the other list begins in A15 on the "anet" tab. Can someone help me understand why it is deleting some of the rows returning an indexOf of -1, but not all of the rows I need deleted?

function withdrawnStudent (){
  let lastRowTyler = roster.getLastRow();
  let tylerData = roster.getRange(3,1,lastRowTyler,1).getValues();
  let tylerArray = tylerData.map(function(r){ return r[0]});

  
  let anetLastRow = anet.getLastRow();
  
 
  let anetLastColumn = anet.getLastColumn();
  let anetData = anet.getRange(15,1,anetLastRow,anetLastColumn).getValues();
  let anetIDArray = anetData.map(function(r){ return r[0]});'''

  

  for (let index = 14; index < 200; index++){
  
    if(tylerArray.indexOf(anetIDArray[index][0]) === -1){
       anet.deleteRow(index +14);
      
     Logger.log(tylerArray.indexOf(anetIDArray[index][0]))

Here is a link to an example spreadsheet. In the "roster" tab, it lists 4th grade student IDS. In the "anet" tab, all rows with a number should be deleted because these are 5th grade IDs. However, not all rows are getting deleted, only some.

https://docs.google.com/spreadsheets/d/1vDse6X6gs3bkgnlBfgo-vzERkAMud3rUDC6j8fEkcrk/edit#gid=447751616

Upvotes: 0

Views: 814

Answers (1)

Da Mahdi03
Da Mahdi03

Reputation: 1608

So when the document changes, set up a trigger to fire your script, and your script will loop through all the available IDs in the first sheet and save them to an array. Then in your second sheet, you will loop through the IDs, and if it is not in the array, then delete the row. We want to make sure that we run the loop backward because if we delete rows and keep moving down, the chart will be skipping rows here and there since the table has shifted upwards.

Here's what I was able to come up with:

function withdrawStudent() {
    //Get Student IDs From Roster Spreadsheet
    var rosterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Roster");
    var dataRangeOnRosterSheet = rosterSheet.getDataRange();
    //Returns a nested Array of all values in the 3rd row, 1st column, all the rows to the end, only one column
    //I added the flat() to make it into a one-dimenstional array
    var studentIDs = rosterSheet.getRange(3, 1, dataRangeOnRosterSheet.getLastRow() - 1, 1).getValues().flat(); 
    Logger.log(JSON.stringify(studentIDs)); //If you want to see what the data looks like
    

    //Now loop through each student ID in the second sheet, and if it doesn't exist in our first array then delete the row
    var ANetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ANet");
    var dataRangeOnANetSheet = ANetSheet.getDataRange();
    var lastRow = dataRangeOnANetSheet.getLastRow();
    var firstRow = 15;
    //Reverse the for loop to work bottom-up because row deletion shifts the chart
    for (var i = lastRow; i >= firstRow; i--) {
        var currentStudentID = ANetSheet.getRange(i, 1, 1, 1).getValue(); //Get Student ID of current row
        //If the currentStudentID is not found in our list of student IDs, remove it
        if (!studentIDs.includes(currentStudentID)) {
            //Remove the row
            ANetSheet.deleteRow(i);
        }
    }

}

How to set up your trigger so that it runs your function every time a user edits the chart: Trigger Menu Add Trigger Popup

Disclaimer: I made a copy of your document so I could test my code and make sure it works, but I'm deleting it now. Hope you are fine with that!

Upvotes: 1

Related Questions