retrieving google sheets cell for calendar returns "last updated"

Question

I currently have a google sheets script that's automated to create a google calendar event daily at a specific time from a google sheets cell. When I execute it manually, it works. But when I allow it to automatically execute at that specific time it simply creates a calendar event with the title "last updated." Any idea why? I've included the code below:

function sheets_to_calendar(){

var mycal = "[email protected]";
var event = CalendarApp.getCalendarById(mycal);
  
var Title = SpreadsheetApp.getActiveSheet().getRange('J1').getValue();
var date = new Date();
var description = SpreadsheetApp.getActiveSheet().getRange('F2').getValue();
  
event.createAllDayEvent(Title, date, {description: description});    
  
}

Is the string "last updated" being assigned somehow to your range J1? Is your trigger really calling that function instead of a different one? I've tried your function and it's working fine for triggers as well. — mshcruz
You know, it looks like what's happening is it's pulling the cell J1 from the first tab of the spreadsheet, rather than the second as I intended. Do you know if there's a way to specify a cell on the specific tab, instead of the "active sheet?" Since it seems to just use the first tab when it's automated. — user2121804

Jose Vasquez Jose Vasquez · Accepted Answer · 2020-12-07T09:16:10

You should check how many triggers are in your project

In order to avoid your code running twice at the specific time you mentioned, keep in mind Google Calendar events are separated in 30 minutes time range, in other words, e.g. if there are 2 triggers running at 08:00 AM and 08:29 AM they are still the same Calendar event, that's why you get your event updated.

If the error persists, try to create your trigger programatically

As I can see in your question, you are managing time-driven triggers, for that reason as a workaround you can create a temporary function in your project and create your trigger using code:

/**
 * Creates two time-driven triggers.
 */
function createTimeDrivenTriggers() {
  // Trigger every 6 hours.
  ScriptApp.newTrigger('myFunction')
      .timeBased()
      .everyHours(6)
      .create();

  // Trigger every Monday at 09:00.
  ScriptApp.newTrigger('myFunction')
      .timeBased()
      .onWeekDay(ScriptApp.WeekDay.MONDAY)
      .atHour(9)
      .create();

Which is recommended because using new Date() is using the time on server.

retrieving google sheets cell for calendar returns "last updated"

1 Answers

You should check how many triggers are in your project

If the error persists, try to create your trigger programatically

References