Loop Assistance

[kelo...@sewanee.edu]

Hi Everyone,

In this script, after an event has been added to Google calendar the value of a cell is set to "done" so that that row can be skipped in the next loop.

For some reason, all of the cells in my column are set as "done" instead of just the row that contains data. 

I don't know what I am doing wrong. Another pair of eyes would be super helpful.

Here is the script:

function createCalendarEvent() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var calendarId = sheet.getRange("AF2").getValue();

  var calendar = CalendarApp.getCalendarById('');

 

  var startRow = 2;  // First row of data to process - 2 exempts my header row

  var numRows = sheet.getLastRow();   // Number of rows to process

  var numColumns = sheet.getLastColumn();

 

  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);

  var data = dataRange.getValues();

 

  var complete = "Done";

 

  for (var i = 0; i < data.length; i++) {

    var row = data[i];

    var date = new Date(row[0]);  //start date

    var bannerid = row[1]; //Banner ID number

    var firstname = row[2];  //First Name

    var lastname = row[3]; //Last Name

    var notes = row[18];  //Notes

    var eventID = row[19]; //event marked Done

    var title = "New Hire Account Creation"

   

    if (eventID != complete && date != ""){

         calendar.createEvent(title, date, date, {

        description: bannerid + '\r' + firstname + '\r' + lastname + '\r' + date + '\r' + notes

      });

    

           sheet.getRange(i+2,20).setValue(complete)

    }

  }

}

Thanks,

Kerline

[CBMServices Web]

You did not share the spreadsheet so not sure if in the case of there is no data in a row, would there still be a date in that row or not?

The if statement seems to only care if there is a date in that row or not (and if marked complete previously).

Also, as a suggestion, rather than marking row as done, suggest you store the eventid of the calendar event. That way if you need to change or cancel event in future, you can use the event ID to do so.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5ca596ff-7156-4e70-a6fe-6c6415a4035dn%40googlegroups.com.

[kelo...@sewanee.edu]

Hello George!

The rows without data do not have a date. 

Here is the link to the spreadsheet: https://docs.google.com/spreadsheets/d/1T6J3Xdt2CnyXMJ9tUNQrvsKAibvjMKQqoC4msuUBuVU/edit?usp=sharing

I am marking the row as done so that it won't add the same event over and over again. If there is a better way to do this, I am open to suggestions.

Thanks again, George.

Kerline

[cbmserv...@gmail.com]

I think I see what is going on. The date is a variable that is initialled by your spreadsheet data (position 1). Once you initialize a date value, it is no longer equal to a blank or (“”).

 

Change that if statement to the following:

 

    if (eventID != complete && row[0] != ""){

 

That should work.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/54d23bf2-374a-4f04-93e3-250dba07c2e7n%40googlegroups.com.

[Martin Molloy]

In line 18 you create a new date object from the contents of the data in column A

If column A is blank it still creates the date object with a value of 'Invalid Date'

In line 26 you are checking that date !=""   which will always be TRUE - even if it is an invalid date

you could get it to work by changing line 26 from:

    if (eventID != complete && date != ""){

to

    if (eventID != complete && row[0] != ""){

Hope that helps

Martin

[Alan Wells]

Check the value for the date as soon as you have the row data and if there is no date then continue the loop,

There's no point in running the rest of the code if there is no date. It will decrease your run time also.

You can use Logger.log() statements to see what your code is doing. In the example below it will print to the log whether the test is true or not.

  for (var i = 0; i < data.length; i++) {

    var row = data[i];

    

    if (!row[0]) {continue;}

    

    var date = new Date(row[0]);  //start date

    var bannerid = row[1]; //Banner ID number

    var firstname = row[2];  //First Name

    var lastname = row[3]; //Last Name

    var notes = row[18];  //Notes

    var eventID = row[19]; //event marked Done

    var title = "New Hire Account Creation"

   

    Logger.log(typeof date)

    Logger.log(eventID != complete)

    Logger.log(eventID !== complete)

    

    if (eventID != complete){

[kelo...@sewanee.edu]

I never would have figured that out on my own. Thank you for your help everyone!