Google-sheets – Create events from a spreadsheet and write relevant ids back to the spreadsheet

google sheetsgoogle-apps-scriptgoogle-calendar

I have a function to create events for field based workers. Eventually I will add this to an onEdit trigger and give the user (centrally based admin worker) some functionality to edit/delete events after they have been created. To do the latter, I believe I will need to be able to isolate the event ID.

Whilst I can write events to a calendar and retrieve the event ID and index of the event just created. I am having difficulty in writing that information back to the spreadsheet (this would be used for edit/delete later in my program).

ws.getRange(4,13,indexes.length,1).setValues(eventIds); allows me to write ids to the spreadsheet but they are not writing to the correct row where the event was created.

How can I use the index stored in const indexes = []; to tell the function the correct row to write the id to. Or is there a better way to achieve this?

Any help would be amazing, I am still very much in the learning stage with Google Apps Script. I have tried to research the problem to no avail and I was hoping that the wisdom of others might be able to show me where I am going wrong.

function createEvents(){

const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheetByName("Sheet Name");
const lr = Math.max.apply(0, ws.getRange('b:b').getValues().map(function (v, i) { if (v != '') { return i } else { return 0 } })) + 1;
const lc = ws.getLastColumn();
const cal = CalendarApp.getCalendarById("myCalId");

const allData = ws.getRange(4,2,lr-3,lc).getValues();
const eventIds = [];
const indexes = [];


allData.forEach(function(r,idx){

const startDate = new Date(r[4]);
const sDateHrs = startDate.setHours(8,0);
const endaDate = new Date(r[6]);
const eDateHrs = endaDate.setHours(18,0);

if(r[10] === true && r[7] === 'In Progress'){

  var event = cal.createEvent(r[0], new Date(sDateHrs), new Date(eDateHrs), {location: r[1], description: r[3] });  
  var eventId = event.getId();

  eventIds.push([eventId]);//pushing event ids to an array to call on later
  indexes.push([idx]);//pushing the index of each id to use when writing the ids back to the spreadsheet


  ws.getRange(4,13,indexes.length,1).setValues(eventIds); //places event ids into spreadsheet but not in the correct rows. I want the ids to be written to rows corresponding to the indexes array

  return event;}});


}

Best Answer

It may be fastest to just get all the data, modify it, and write all the event IDs and checkbox values each with one setValues() call. Try this:

function createEvents() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  const column = { // zero-indexed; A = 0, B = 1, C = 2
    title: 1,
    location: 2,
    auditor: 4,
    startDate: 5,
    endDate: 7,
    status: 8,
    checkbox: 11,
    eventId: 14,
  };
  const cal = CalendarApp.getCalendarById('myCalId');
  const range = sheet.getRange(1, 1, getLastRow_(sheet), sheet.getLastColumn());
  const formulas = range.getFormulas();
  const data = range.getDisplayValues().map((row, rowIndex) => {
    if (!row[column.eventId]) {
      row[column.eventId] = null;
    }
    if (row[column.checkbox] === true
      && row[column.status] === 'In Progress'
      && row[column.startDate] && row[column.startDate].setHours
      && row[column.endDate] && row[column.endDate].setHours) {
      row[column.checkbox] = false;
      row[column.eventId] = cal.createEvent(
        row[column.title], row[column.startDate].setHours(8, 0), row[column.endDate].setHours(18, 0),
        { location: row[column.location], description: row[column.auditor] }
      ).getId();
    }
    return row.map((value, columnIndex) => formulas[rowIndex][columnIndex] ? formulas[rowIndex][columnIndex] : value);
  });
  range.offset(0, column.eventId, data.length, 1)
    .setValues(data.map(row => [row[column.eventId]]));
  range.offset(0, column.checkbox, data.length, 1)
    .setValues(data.map(row => [row[column.checkbox]]));
}


/**
* Gets the position of the last row that has visible content in a column of the sheet.
* When column is undefined, returns the last row that has visible content in any column.
*
* @param {Sheet} sheet A sheet in a spreadsheet.
* @param {Number} columnNumber Optional. The 1-indexed position of a column in the sheet.
* @return {Number} The 1-indexed row number of the last row that has visible content.
*/
function getLastRow_(sheet, columnNumber) {
  // version 1.5, written by --Hyde, 4 April 2021
  const values = (
    columnNumber
      ? sheet.getRange(1, columnNumber, sheet.getLastRow() || 1, 1)
      : sheet.getDataRange()
  ).getDisplayValues();
  let row = values.length - 1;
  while (row && !values[row].join('')) row--;
  return row + 1;
}

I added a line that unticks checkboxes on rows where an event has been created so that you do not get multiple copies of events in the calendar.

You cannot run your function on the onEdit(e) simple trigger because the use of CalendarApp requires authorization which is unavailable in that context. You can run it through an installable trigger, but because the code will then run under the account that installed the trigger, you will again have to test carefully to ensure that the events end up in the calendar you intended. It may be easiest to run the code through a menu item or a button so that runs under the account of the user at the keyboard.

Related Topic