automatically run a function to create a new event in google script when a new row is added to google sheet without adding multiple events

JenEve83 0
Viewed 18

I would like to automatically run a function to create a new event in google script when a new row is added to google sheet without adding multiple events. I just want the the new event to be created. My script works but it adds ALL the rows from my sheet again instead of just the newest one added.

function createCalendarEvent() {
  let communityCalendar = CalendarApp.getCalendarById("dlv2d6fo8n552173u2ifl7qn9g@group.calendar.google.com")
  let sheet = SpreadsheetApp.getActiveSheet();

  let scheduleEvent = sheet.getDataRange().getValues(); 
  
  scheduleEvent.splice(0 ,1);

  scheduleEvent.forEach(function(entry){
    communityCalendar.createEvent(entry[1], entry[2], entry[3])
  });
}

I set a trigger to run the function, but like I said it runs the function for the whole sheet not just the newest row (or event) added

google-sheets google-apps-script javascript
1 Answers

In your situation, how about the following modification?

Modified script:

function createCalendarEvent() {
  let communityCalendar = CalendarApp.getCalendarById("dlv2d6fo8n552173u2ifl7qn9g@group.calendar.google.com");
  let sheet = SpreadsheetApp.getActiveSheet();
  let value = sheet.getRange(sheet.getLastRow(), 2, 1, 3).getValues()[0];
  if (value.includes("")) return;
  communityCalendar.createEvent(...value);
}

  • By this modification, the values are retrieved from the last row of the sheet.

  • When the latest value is row 2, please modify getRange(sheet.getLastRow(), 2, 1, 3) to getRange(2, 2, 1, 3).

  • If the last row of the sheet is different from the columns "B" to "D", please test the following modified script.

      function createCalendarEvent() {
    Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) { // Ref: https://stackoverflow.com/a/44563639
      const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
      return search ? search.getRow() : offsetRow;
    };

    let communityCalendar = CalendarApp.getCalendarById("dlv2d6fo8n552173u2ifl7qn9g@group.calendar.google.com");
    let sheet = SpreadsheetApp.getActiveSheet();
    let value = sheet.getRange(sheet.get1stNonEmptyRowFromBottom(2), 2, 1, 3).getValues()[0];
    if (value.includes("")) return;
    communityCalendar.createEvent(...value);
  }

  • If you want to use OnEdit trigger, how about the following sample script? Before you use this script, please install OnEdit trigger to the function createCalendarEvent. Ref When you use this script, please edit the cells of the sheet. By this, the script is run by the installable OnEdit trigger. In this script, please don't directly run the script, because an error occurs. Please be careful about this.

      function createCalendarEvent(e) {
    const sheetName = "Sheet1"; // Please set your sheet name.
    const { range } = e;
    const sheet = range.getSheet();
    const value = sheet.getRange(range.rowStart, 2, 1, 3).getValues()[0];
    if (sheet.getSheetName() != sheetName || value.includes("")) return;
    let communityCalendar = CalendarApp.getCalendarById("dlv2d6fo8n552173u2ifl7qn9g@group.calendar.google.com");
    communityCalendar.createEvent(...value);
  }

Reference:

Tanaike0
Related