Inserting Calender ID in Google Sheet

Question

I am trying to export events from google sheet to google calender using code I found Here

I have updated it to fit my sheet but the stock code replaced formulas in cells with the value, rendering the sheet useless. I tried replacing the last line

range.setValues(data);

with

range.setFormulas(data);

but this just made all cells blank.

Is there away to use the .setValues to just set the Id?

Coppy of my code forgot to add it

 function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "abeaqo4gf3of7u64sa8uufulpc@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[21]);  // First column
    var title = row[22];           // Second column
    var tstart = new Date(row[23]);
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[24]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var loc = row[25];
    var desc = row[26];
    var id = row[27];    // Sixth column == eventId
    var tep = row[28];
    if (i > tep) continue;
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    if (!event) {
      //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
      row[27] = newEvent;  // Update the data array with event ID
    }
    else {
      event.setTitle(title);
      event.setDescription(desc);
      event.setLocation(loc);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
    }
    debugger;
  }
  // Record all event IDs to spreadsheet

}

SGC · Answer 1 · 2014-12-18T22:37:13.273

Instead of setting all the data outside the for loop, you can get the particular cell (where you want to update the event id) using range and set the value to that cell in each row.

Here is the code. you can just change the row and column values accordingly:

  function exportEvents() {
     var sheet = SpreadsheetApp.getActiveSheet();
     var headerRows = 1;  // Number of rows of header info (to skip)
     var range = sheet.getDataRange();
     Logger.log(range.getLastRow());
     var data = range.getValues();
     Logger.log(data);
     var calId = "your calendar id";
     var cal = CalendarApp.getCalendarById(calId);
     for (i in data) {
       if (i < headerRows) continue; // Skip header row(s)
        var row = data[i];
        var date = new Date(row[0]);  // First column
        var title = row[1];           // Second column
        var tstart = new Date(row[2]);
        tstart.setDate(date.getDate());
        tstart.setMonth(date.getMonth());
        tstart.setYear(date.getYear());
        var tstop = new Date(row[3]);
        tstop.setDate(date.getDate());
        tstop.setMonth(date.getMonth());
        tstop.setYear(date.getYear());
        var loc = row[4];
        var desc = row[5];
        var id = row[6];              // Sixth column == eventId
        // Check if event already exists, update it if it does

        var event = cal.getEventSeriesById(id);
        //Logger.log(id);

        range =  SpreadsheetApp.getActiveSheet().getRange(i, 6);

        if (!event) {

      var newEvent = cal.createEvent(title, tstart, tstop,{description:desc,location:loc}).getId();
      //row[6] = newEvent;  // Update the data array with event ID
      range.setValue(newEvent);
    }
    else {
      event.setTitle(title);
      event.setDescription(desc);
      event.setLocation(loc);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
        range.setValue(event.getId());
    }
     //sheet.getRange(i,6).setValue(newEvent); 
   }

 }

Inserting Calender ID in Google Sheet

1 Answers1