Troubleshoot script to maintaining/updating Google Calendar with Google Spreadsheet including automatically changing colors

Question

I am trying to write a Google app script to accomplish the following:

Update a Google calendar based on a couple of columns in a Google spreadsheet that signify:
a. whether the event has a date specified (column "date" != " ")
b. whether the event has already been posted to the calendar (column "In Calendar" = " ").
Once the event has been added to calendar, I would like to add "Yes" to a column in the spreadsheet (column "In calendar).
If possible, automatically change color of event based on attributes in a specific column (i.e. if column "State" = NM, then the color of event is blue, if "State" = MT, then the color of event is purple, etc.)

I've looked at various postings throughout the web (mainly http://blog.ouseful.info/2010/03/07/maintaining-a-google-calendar-from-a-goole-spreadsheet-reprise/ and http://blog.ouseful.info/2010/03/04/maintaining-google-calendars-from-a-google-spreadsheet/),

I have hobbled together the following script:

var IN_CAL = "Yes";

function caltest() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var data = sheet.getDataRange().getValues();   // Process any rows with data
  var cal = CalendarApp.getCalendarsByName("Oil and Gas Test 3")[0];
  for (i in data) {
    var row = data[i];
    var state = row[2] // 3rd column, column with state
    var desc = row[5]; // 6th column, column with lease description
    var date = row[4]; // 5th column, column with date
    var title = state+": "+desc;
    var inCalendar = row[1]; // 2nd column, tells whether in calendar or not
    if ((inCalendar != IN_CAL)&&(date != " ")) { // Prevents adding duplicates to calendar  
      cal.createAllDayEvent(title, new Date(date))
      var v = parseInt(i)+1;
      sheet.getRange(v, 2, 1).setValue(IN_CAL);
      SpreadsheetApp.flush(); // Makes sure the cell is updated right away in case the script is interrupted
    }
    }
  }

In case it helps, the columns of my spreadsheet are in the following order:

Project Number | In Calendar | State | Lease Date | Date | Description.

I reference "In Calendar", "State", "Date", and "Description"

in my script.

It seems to mostly work in that it adds the events to the calendar, except that:

The script is adding "Yes" to all cells in column "In Calendar" regardless of whether or not there is a date in the column "date"; and
I haven't even attempted to adjust the colors.

Does anyone have any tips on how to get the script to read both the "In Calendar" and "Date" columns when deciding whether a event should be created and the "In Calendar" column should be updated to "Yes"? I know that an event probably won't be created without a date, but I don't want the rows without a date to read "Yes" in the "In Calendar" column. I also want the script to read the entire "In Calendar" column that has data, not a predefined number of rows.

Also, any tips on how to automatically change the colors of individual events?

Please let me know if you need additional information.

Alan Wells Alan Wells · Accepted Answer · 2015-03-18T02:39:20

Add a breakpoint to your code, by clicking a line number. You'll get a red dot. See line 12.

Break Point

Then click the icon of a bug.

Click Bug

You'll see all the variable values being shown in a window at the bottom of the screen.

Debug Break

Click the 'Step In' icon:

Step In

If the date in your spreadsheet cell is formatted as a date, the date value should be something like this:

Thu Mar 12 2015 19:01:08 GMT-0400 (EDT)

If the cell is formatted as a string, then the test needs to be like this:

if (date != "") {

But, in any case, step through the code, and view the data type and value of the date variable. (And inCalendar variable) Depending on what value is actually being retrieved, will determine what check you need to make with the condition.

I'm guessing that:

(date != " ")

Is always evaluating to true

Troubleshoot script to maintaining/updating Google Calendar with Google Spreadsheet including automatically changing colors

1 Answers