CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user1292411
user1292411

Reputation: 85

Get active row inside Array

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"
    }  
 }

Upvotes: 1

Views: 860

Answers (2)

Mogsdad
Mogsdad

Reputation: 45710

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.

Upvotes: 1

Cameron Roberts
Cameron Roberts

Reputation: 7367

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.

Upvotes: 2

Related Questions