Function not running in GAS

Question

I am trying to create a script to delete rows with specific data in Col 1 of a Google Sheet. It involves creating an array and having it read through the cell in that Column and decide if it should be deleted. I can't seem to get it working. I have gotten a good start but I feel like I am missing something.

I have tried basing my function similar to the format as the answer in Delete a row in Google Spreadsheets if value of cell in said row is 0 or blank, but I appear to be missing something. I have a feeling it is due to it not actually reading through the array but I can't figure out how to correct it that way it does read the data.

I have looked at other github sources such as https://gist.github.com/dDondero/285f8fd557c07e07af0e but I am stuck.

function deleteRows()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var RANGE = ss.getRange('B:B');
var v = RANGE.getValues();

var trainer = [""];
for(var i = v.length-1; i >= 0; i--)
{
  if(v[0,i] = trainer)
  {
    ss.deleteRow(i + 1);
  }
}
}

What should happen if anything in the list is in Col 1 that it should delete the row that data is present in. As of right now it doesn't remove the row if something in the specified array. Doesn't provide any errors and runs fine as I have a function in this script that currently timestamps the rows once something has been inputted.

The data returned from `getValues()` is an array of arrays and the data in a one column array is accessed with `value[i][0]`. You can iterate from the bottom to the top or you can iterate from top to bottom if you just keep track of how many rows were deleted as I did below. Also `ss.getRange('B:B') doesn't really specify the sheet although it will default to `SpreadsheetApp.getActive().getSheets()[0];` — Cooper, Sep 11 '19 at 22:07
the value range retrieved with `getValues();` is a 2-D matrix whose elements are accessed in the order [row][column]. So if you want to loop through rows, the correct syntax is `v[row][column]` i.e. `v[i][0]`. Btw, you say you want to check values in Col1 - so that is column `A`, not `B`. — ziganotschka, Sep 12 '19 at 09:02
If I understand correctly @ziganotschka that the columns start from 0 so B should be 1. Thats what I have come to reading the Google Sheets documentation? — Average Joe, Sep 12 '19 at 13:34
The start index is 0 as far as arrays are concerned - the first value of a valuerange will be `valuerange[0][0]`. When it comes ranges (columns) themselves, the first index is 1, e.g. `getRange('A1')` is equivalent to `getRange(1,1)` — ziganotschka, Sep 12 '19 at 13:53
Oh okay I understand. That makes sense. I was getting confused then! Thanks for the correction. — Average Joe, Sep 12 '19 at 13:59

Cooper · Answer 1 · 2019-09-11T22:12:22.557

0

Try this:

It looked like you were trying to delete rows that didn't have any value in column B. That's what this does.

function deleteRows() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getRange(1,2,sh.getLastRow(),1);
  var v=r.getValues();
  var d=0;
  for(var 1=0;i<v.length;i++)  {
    if(!v[i][0]) { 
      ss.deleteRow(i+1-d++);
    }
  }
}

edited Sep 11 '19 at 22:12

answered Sep 11 '19 at 22:03

Cooper

59,616
6
23
54

This appears to work if there isn't anything in the column cell I am looking at. How would you change this to read through an array or a list persay and if it reads something that is in the array in Column B to delete this column. I think the array is what I am struggling with. – Average Joe Sep 12 '19 at 13:47
1

Just remove the exclamation point in the if. Review JavaScript truthy,falsy. – Cooper Sep 12 '19 at 17:37

score 0 · Accepted Answer · answered Sep 12 '19 at 17:11

Thank you for all the help. I was able to get to the bottom of it and made a change to the information that was provided to come to my solution. It may not be the cleanest but it works for what is needed. I got both the timestamp I had previously working and the delete row function to work. Thank you again for the help!

function onEdit(event) 
{ 
  var d = [""];

  var timezone="GMT-5";
  var timestamp_format="hh:mm:ss a"; // Timestamp Format. 
  var updateColName="Advisor";
  var teamLeadColName = "Team Leader";
  var timeStampColName="Time";
  var sheet = event.source.getActiveSheet();

  var actRng = event.source.getActiveRange();
  var editColumn = actRng.getColumn();
  var headers = sheet.getRange(2, 1, 1, sheet.getLastColumn()).getValues();
  var teamLeadCol = headers[0].indexOf(teamLeadColName);
  var timeCol = headers[0].indexOf(timeStampColName);
  var updateCol = headers[0].indexOf(updateColName); 
  updateCol = updateCol+1; 
  for(var index = actRng.getLastRow(); index >= actRng.getRow(); index--)
  {
    if(teamLeadCol > -1 && index > 1)
    {
      var cell = sheet.getRange(index, teamLeadCol + 1);
      var teamLeadName = cell.getValue();
      for(var i=0; i < d.length; i++)
      {
        if(d[i] == teamLeadName)
        {
         sheet.deleteRow(index);
          continue;
        }
      }

    }
    if (timeCol > -1 && index > 1 && editColumn == updateCol) // only timestamp if 'Last Updated' header exists, but not in the header row itself!
    { 
      var cell = sheet.getRange(index, timeCol + 1);
      var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
      cell.setValue(date);
      var datestamp_format = "MMMM-dd-yyyy";
      var dateStampColName = "Date";
      var dateCol = headers[0].indexOf(dateStampColName);
      if(dateCol > -1 && index > 1 && editColumn == updateCol)
      {
        var cell = sheet.getRange(index, dateCol +1);
        var calendarDate = Utilities.formatDate(new Date(), timezone, datestamp_format);
        cell.setValue(calendarDate);
      }
    }
  }
}

Function not running in GAS

2 Answers2