how to write if statement to answer true only when the date cell, formatted as a date, is empty?

Question

I am writing a Google app script that will update a Google calendar based on a) whether the event has a date specified (column "date" != " "), and 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").

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/), and have figured out 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 5")[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
        }
    }
}

The script works except that it is adding "Yes" to all cells in column "In Calendar" regardless of whether or not there is a date in the column "date". The date cell in my spreadsheet is formatted as a date so I understand that the if statement is always evaluating to be true because it is looking for a string that will never be there. How do I write:

if ((inCalendar != IN_CAL)&&(date !== " " ))

to answer true only when the date cell, formatted as a date, is empty?

This would be helpful to verify if the cell value is date or not. http://stackoverflow.com/questions/15347237/run-custom-function-if-value-in-cell-is-date — KRR, Mar 25 '15 at 17:01

score 0 · Answer 1 · answered Mar 25 '15 at 17:25

it appears that you have a space between the quotes where you are testing for empty.if this is true what you are actually testing for is that is not equal to space and this is different from testing for empty.

if ((inCalendar != IN_CAL)&&(date !== " " ))

should be:

if ((inCalendar != IN_CAL)&&(date !== "" ))

without the space between the final set of quotes.

how to write if statement to answer true only when the date cell, formatted as a date, is empty?

1 Answers1