0

I am trying to create full day event in google calendar using google sheet. I have used the following script and it is working fine with my sheet but it has added event in the previous date:

entries in the sheet have different dates

enter image description here

// function to retrieve data from Sheet and add to Calendar 
function simpleSheetsToCalendar() { 
   
  // get spreadsheet 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Sheet1'); 
   
  // get the data from Google Sheet 
  var data = sheet.getRange(sheet.getLastRow(),1,1,2).getValues(); 
  
  //  create variables 
  var date = data[0][0]; 
  var title = data[0][1]; 
 
  // get calendar 
  var masterCal = CalendarApp.getCalendarById('c_mihq1ibbj3lof0bcfqbnvn6vds@group.calendar.google.com'); 
   
  // add to calendar 
  masterCal.createAllDayEvent(title,date);   
   
} 
3

1 Answer 1

0

I invite others to correct or clarify my comments about the cause and effect of timezones discussed in this answer.

The problem of spreadsheet dates not aligning with Calendar dates is well known - there are too many references to list (even on WebApps and StackOverflow). The Spreadsheet, Calendar AND AppsScript Project each have their own timezone and this is understood to be the root cause of the problem. The timezone for the AppsScript defaults to GMT-0500 (Eastern Standard Time).

The solution (if that is the right word) is:

  • convert the spreadsheet date to a simple YYYY/MM/DD format (without timezone) Utilities.formatDate(date, SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), 'yyyyMMdd')
  • rebuild a date object using the year, month and day from the date
    var year = +thiseventdate.substring(0, 4)
    var month = +thiseventdate.substring(4, 6)
    var day = +thiseventdate.substring(6, 8)
    var adjeventdate = new Date(year, month - 1, day)

The reason for month - 1 is that the month numeration starts from 0. And the + sign before function just converts string, which is returned by functions, to numbers so we can pass them right into new Date().

HT: @VyacheslavPukhanov from StackOverflow Convert a string to date in google apps script

This answer adds two other features:

  • test whether there are any events to process
  • process all events on the spreadsheet

// function to retrieve data from Sheet and add to Calendar 
function mutiplesimpleSheetsToCalendar() { 
   
  // get spreadsheet 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Sheet1'); 

  // variables-number of events
  var startRow=2
  //Logger.log("DEBUG: the first row is "+startRow+", last row is "+sheet.getLastRow())
  var numRows = sheet.getLastRow()-startRow+1
  //Logger.log("DEBUG: Number of rows = "+numRows)
  
  if (numRows ==0){
    // number rows = 0
    // stop processing
    // Logger.log("DEBUG: No events to process")
    return
  }

  // get calendar 
  var masterCal = CalendarApp.getCalendarById('c_mihq1ibbj3lof0bcfqbnvn6vds@group.calendar.google.com'); 

  // get the data from Google Sheet 
  //var data = sheet.getRange(sheet.getLastRow(),1,1,2).getValues(); 
  var data = sheet.getRange(startRow,1,numRows,2).getValues(); 
  //Logger.log("DEBUG: the range is "+sheet.getRange(startRow,1,numRows,2).getA1Notation())
  
  for (var i = 0; i<numRows;i++){
  //  create variables 
  var date = data[i][0]; 
  var title = data[i][1]; 
    
  // convert date to spreadsheet timezone
  // creates a string
  var thiseventdate = Utilities.formatDate(date, SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), 'yyyyMMdd')
  //Logger.log("DEBUG: event date (string) = "+thiseventdate+", title = "+title)

  // parse date string to date object
  var year = +thiseventdate.substring(0, 4)
  var month = +thiseventdate.substring(4, 6)
  var day = +thiseventdate.substring(6, 8)
  var adjeventdate = new Date(year, month - 1, day)
  //Logger.log("DEBUG: the parsed date string is "+adjeventdate+", title = "+title)
  // end parse date string

  // add to calendar 
  masterCal.createAllDayEvent(title,adjeventdate);   
  //Logger.log("DEBUG: Entry Done")

  }

} 

Raw data

rawdata


Calendar

calendar

4
  • thank you for the detailed response. It worked. I was looking for this integration for very long.
    – Ali Silat
    Dec 29, 2021 at 20:19
  • If the answer was helpful, then you might consider accepting it.
    – Tedinoz
    Dec 30, 2021 at 1:01
  • I just checked the icon. I accepted it now (:
    – Ali Silat
    Dec 30, 2021 at 15:24
  • Where in the code do you specify which column the dates data is, and which column the desired event titles are?
    – gills
    Nov 16 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.