Showing posts from June, 2017

Google Script - Create Calendar Events from Spreadsheet Data

Yesterday I shared how I added reminder dates to a spreadsheet for a person in my department who keeps track of software subscriptions.  Today I'm sharing how we take the entries on that spreadsheet and create calendar events, so the reminders are built into her calendar.
Here are the steps to create calendar events from spreadsheet data:

1. Either open the spreadsheet with the data (or create a new spreadsheet & populate it with data).
This is the spreadsheet where I first added the script to add a "remind date" (yesterday's post).

2.  Open the Script Editor
Go to the Tools Menu and select Script editor

3. Replace the Code
Replace the default code -

with this - (all the text highlighted in gray -  including the final bracket)

function createCalendarEvent() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var calendar = CalendarApp.getCalendarById('calendarID');

  var startRow = 2;  // First row of data to process - 2 exempts my header row
  var numRows = sh…

Google Script - Setting up Reminder Dates

A person in my department keeps track of software subscriptions, and asked if there was a way to automatically have it provide a reminder 45 days prior to the renewal dates.  I said sure! Once I set up the addition of a "reminder date", I modified an existing script to have it write that to her calendar.  So now she simply inputs the vendor information, purchase date & renewal date, and then the scripts calculate the reminder date and adds it to the calendar.  It seems to work pretty slick!

Here are the steps to create the reminder dates with a script -

1. Create the spreadsheet where your data will reside.
This can be a stand-alone spreadsheet, or it can be one that is populated by a form.  Here is my sample-

2. Open up the Script editor
Go to the Tools Menu and select Script editor

3.  Replace the code
Replace the default code -

with this - (all the text highlighted in gray include the final bracket)
var dateIndex = 6; //cell with reference date

var ss =…