CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Robert Kiss
Robert Kiss

Reputation: 1

How to reference a cell in google script?

I'm new to google script and have little experience, so I hope you can help me. 

I have this script: 

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Staffing Data');
  var r = s.getRange('B:B');
  var v = r.getValues();
  for(var i=v.length-1;i>=0;i--)
    if(v[0,i]=='DE')
      s.deleteRow(i+1);
};

And I use it to identify if the column B contains the given value. Would it be possible to have the 'DE' reference to a cell in the spreadsheet? I would like it to reference the cell A1 in the sheet "Staffing Data", this way I could change the value directly from there without going into the script.

Thanks in advance

Upvotes: 0

Views: 721

Answers (1)

Cooper
Cooper

Reputation: 64110

for(var i=v.length-1;i>=0;i--) if(v[i][0]=='DE') s.deleteRow(i+1);

Also this var r = s.getRange(1,2,s.getLastRow()); is better than this var r = s.getRange('B:B'); as the latter often returns nulls from bottom of data to maxrows.

Just to be clear: var startRow=2;var r= s.getRange(sr,1,s.getLastRow()-startRow+1); if your range is not starting at the top

You can also delete like this var d=0;for(var i=0;i<v.length;i++) if(v[i][0]=='DE') s.deleteRow(i+1-d++);

Upvotes: 2

Related Questions