Get active row inside Array

Question

I have this Google Apps Script to read rows from a Google Spreadsheet but I need to find out the row number of the active array element.

The goal is to check if a row has been processed or not (statusverwerkt variable). If the value of statusverwerkt is x I create a Google event in the calendar and I want to change to value of statusverwerkt to yes

There might be another way to do this, but it would be helpful to find out the current row in the array element.

This is my code:

function caltest1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = SpreadsheetApp.getActiveSheet().getLastRow();
  var Totalrows = sheet.getDataRange();
  var TotalNumrows = Totalrows.getNumRows();
  var Reservations = range-1;
  var startRow = 2;  // First row of data to process
  var numRows = Reservations;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 17);
  var data = dataRange.getValues();
  var cal = CalendarApp.getDefaultCalendar();
  for (i in data) {
    var row = data[i];
    var title = row[2];  // First column
    var desc = row[7];       // Second column
    var tstart = row[9];
    var tstop = row[10];
    var loc = row[3];

    var statusverwerkt = row[14]; 

    if (statusverwerkt = "x"){

    cal.createEvent(title, new Date(tstart), new Date(tstop), {description:desc,location:loc});

    //set statusverwerkt value = "yes"


    }
    else {

   // statusverwerkt = "x"
    }  
 }

score 2 · Answer 1 · answered Jun 02 '15 at 14:14

2

If I understand your question correctly, the row number is readily available by adding 1 to i.

For example, as you loop over the Array of spreadsheet rows you could do:

for (i in data) {
    var row_number = i+1;

The reason for the +1 is that the Array is zero indexed (starts counting at row 0), while the Google Spreadsheet functions start counting at row 1.

answered Jun 02 '15 at 14:14

Cameron Roberts

7,127
1
20
32

I only start processing from row 2. I tried the code anyway and converted row_number to a number. Then I try following code: `SpreadsheetApp.getActiveSheet().getRange('Q',row_number).setValue('verwerkt');` and get error _Cannot convert Q to (class). (line 40, file "ScriptCalendar")_ – user1292411 Jun 02 '15 at 14:39
If you are starting your data at row 2 in the spreadsheet, you'll need to add 2 to i. Row 0 in your Array is row 2 in your spreadsheet. In your comment, you are not using getRange() correctly. You can either pass a single String value EG `getRange('A2') //row 2, column A`or you can pass two numeric values, one for the row and one for the column: `getRange(2,1) //row 2, column 1` . Personally I prefer the latter one because you can just work with numbers, but if you prefer to use "A1" notation, you need to join the row number to your string: `getRange('Q'+row_number);` – Cameron Roberts Jun 02 '15 at 15:25

score 1 · Accepted Answer · edited May 23 '17 at 11:59

Your code appears to have come from this blog by Tony Hirst.

You'll find an updated version of it in Create Google Calendar Events from Spreadsheet but prevent duplicates, which records the event ID to both remember which row has been processed as well as to provide a link back to the calendar event.

Instead of calculating the individual spreadsheet row, it records all updates in the data array, and then updates the spreadsheet in a single operation. This is faster then many writes, with the risk that the script could get interrupted for exceeding the 6 minute execution limit before updating the sheet.

I used the code as in your answer and it really helped me! – user1292411 Jun 02 '15 at 15:26 — user1292411, Jun 02 '15 at 15:26

Get active row inside Array

2 Answers2