0

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;}});


}
0

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.

7
  • Thank you for this @doubleunary. I did test and whilst it succeeded in overwriting the data within the spreadsheet table, it did not create the events or add them to column 13. Also, I have array formulas within the spreadsheet itself so this method of re-writing the data table would not work for me. – Matthew Cooper May 10 at 8:06
  • Consider sharing a publicly editable sample spreadsheet with realistic-looking data. – doubleunary May 10 at 8:26
  • Your sample spreadsheet gives "Access denied." Please share it with "can edit" rights as explained in these instructions. – doubleunary May 10 at 18:21
  • Sorry, the original was locked behind a domain. Please see link I have added my script in the editor and some commented notes. – Matthew Cooper May 11 at 14:06
  • Edited the answer. Looking at the sample spreadsheet, it appears that the column indices in the code were off for some of the columns, which explains why the conditions were never met and no events got created. There are drop-down menus and checkboxes all the way down on about 1000 "blank" rows, so you may want to delete any unneeded blank rows at the bottom, or alternatively use the getLastRow_() function to find the number of rows actually in use. – doubleunary May 11 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.