I am using a Google Script to pull Google Form data from Sheets to make an event in Google Calendar. The form populates two columns of data (timestamp and a number). I use a formula in column C to generate a title from the data in column B. No matter what I try I can only get the data in column B to show up in the calendar.
// 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,3).getValues();
// create variables
var date = data[0][0];
var title = data[0][3];
// get calendar
var masterCal = CalendarApp.getCalendarById('uknowme@gmail.com');
// add to calendar
masterCal.createAllDayEvent(title,date);
}