CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-calendar-api
Smonthei
Smonthei

Reputation: 11

Linking a google sheet to automatically create calendar events and update them when the google sheet is updated

I am trying to link my google sheet to a calendar to automatically create calendar events and update them when they are updated in the google sheet. My google sheet tracks new building opening dates and new building construction start dates so for each row I need it to create two calendar events when applicable (sometimes only one of the dates have been filled out).

The Headings of the sheet are "Loc. #", "Location", "Cons Start", and "Whse Open." The values for each of these headings are populated from a reference to a different sheet and updates automatically from that sheet due to being a reference.

I am not the most inclined in javascript, but so far the code I have for the google apps script is as follows: 

// Calendar ID can be found in the "Calendar Address" section of the Calendar Settings.
var calendarId = '[email protected]';

// Configure the year range you want to synchronize, e.g.: [2006, 2017]
var years = [2017,2020];

// Date format to use in the spreadsheet.
var dateFormat = 'M/d/yyyy H:mm';

var titleRowMap = {
  'loc#': 'Loc. #',
  'location': 'Location',
  'conStart': 'Cons Start',
  'whseOpen': 'Whse Open',
};
var titleRowKeys = ['loc#', 'location', 'conStart', 'WhseOpen'];
var requiredFields = ['loc#', 'location', 'conStart', 'WhseOpen'];

// This controls whether email invites are sent to guests when the event is created in the
// calendar. Note that any changes to the event will cause email invites to be resent.
var SEND_EMAIL_INVITES = false;

// Setting this to true will silently skip rows that have a blank start and end time
// instead of popping up an error dialog.
var SKIP_BLANK_ROWS = false;

// Updating too many events in a short time period triggers an error. These values
// were tested for updating 40 events. Modify these values if you're still seeing errors.
var THROTTLE_THRESHOLD = 10;
var THROTTLE_SLEEP_TIME = 75;

// Adds the custom menu to the active spreadsheet.
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [
    {
      name: "Update from Calendar",
      functionName: "syncFromCalendar"
    }, {
      name: "Update to Calendar",
      functionName: "syncToCalendar"
    }
  ];
  spreadsheet.addMenu('Calendar Sync', menuEntries);
}

// Creates a mapping array between spreadsheet column and event field name
function createIdxMap(row) {
  var idxMap = [];
  for (var idx = 0; idx < row.length; idx++) {
    var fieldFromHdr = row[idx];
    for (var titleKey in titleRowMap) {
      if (titleRowMap[titleKey] == fieldFromHdr) {
        idxMap.push(titleKey);
        break;
      }
    }
    if (idxMap.length <= idx) {
      // Header field not in map, so add null
      idxMap.push(null);
    }
  }
  return idxMap;
}

// Converts a spreadsheet row into an object containing event-related fields
function reformatEvent(row, idxMap, keysToAdd) {
  var reformatted = row.reduce(function(event, value, idx) {
    if (idxMap[idx] != null) {
      event[idxMap[idx]] = value;
    }
    return event;
  }, {});
  for (var k in keysToAdd) {
    reformatted[keysToAdd[k]] = '';
  }
  return reformatted;
}

Not really sure what to do next to make this happen. Any suggestions on how to implement this?

Upvotes: 1

Views: 2227

Answers (1)

KENdi
KENdi

Reputation: 7741

You can try to check these tutorials and SO questions on how to use Google Apps Script in creating a calendar events. These links includes some sample code that you can copy of follow.

For more informationor more ideas, you can also check this tutorials on how to use Google Apps Script in creating a calendar events from Google Form.

Upvotes: 1

Related Questions