Google Scripts how to delete extra rows from an array

Question

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

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. — Allison Bucklew
– Allison Bucklew, Commented Feb 19, 2021 at 21:29
Ok it seems your tables are identical in both sheets, so why can't you just overwrite the table in anet with the table in your roster every time the user makes a change instead of trying to find out which ones are deleted to loop through and delete them manually? — Da Mahdi03
– Da Mahdi03, Commented Feb 19, 2021 at 21:42
@DaMahdi03 Thank you for responding! I am brand new to javascript. This is literally the first thing I've coded. I cannot do what you suggest because, as the school year progresses, students will have assessment data input. I do not want the names to be overwritten if they have been filtered/sorted in the other tab. Students' associated assessment data will then be mixed up. — Allison Bucklew
– Allison Bucklew, Commented Feb 19, 2021 at 21:45

Da Mahdi03 · Accepted Answer · 2021-02-19 22:55:07Z

1

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:

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!

answered Feb 19, 2021 at 22:55

Da Mahdi03

1,6081 gold badge12 silver badges19 bronze badges

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

5 Comments

Allison Bucklew Over a year ago

That was my problem! I needed to run the loop from the bottom! You are a genius!!

Da Mahdi03 Over a year ago

If it's the right answer can you mark it as so?

Allison Bucklew Over a year ago

Thank you! It is totally fine that you made a copy of the document. I appreciate your help. It is strange, when I run your code, and when I used the indexOf method before, some rows remain when I run the script. To test it I put a list where none of the values matched on both sheets. If the script is working, I believe all the populated rows on the anet sheet should be deleted since none of the ids are found on the roster sheet. However, 14 rows remain.

Da Mahdi03 Over a year ago

I ran the code I have written here directly on your spreadsheet just by calling the withdrawStudent() and it removed all your 5th grade IDs

Allison Bucklew Over a year ago

I tried re-typing your code, and I must have made an error. When I copied/pasted, it worked! Thank you!!!

Collectives™ on Stack Overflow

Google Scripts how to delete extra rows from an array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related