"Range not defined" error using getRange() within a forEach loop

Question

I have searched through some similar questions about getRange() errors witin loops and I'm still not sure what is the best solution.

Iterate over range, append string to each Cannot find method getRange(int,int) in Google Sheets

My columns are set up as: Column 1 checkboxes Column 2 Event Column 3 Start Column 4 End Column 5 Description Column 6 Email Column 7 POC Column 8 EventID

This script runs through all the rows that are checked to identify if there is a current eventID, and if not, create a new eventID and set its value.

This is where the error is coming from: EventsData.getRange(row[7]).setValue(newEventID);.

Would you please give me some guidance on how best to formulate the getRange() to include the looping?

function ReplaceOrCreateEvent()
{ 
  var cal = CalendarApp.getCalendarById("calendarID");
  var EventsData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SHEET_NAME');
  var rows = EventsData.getDataRange().getValues();
  var headers = rows.shift();
  rows.forEach(function(row)
  {
    if(row[0])
    {
      Logger.log(JSON.stringify(row));
      var eventID = row[7];
      
      if(eventID != 0)
      {
        var event = cal.getEventById(eventID);
        event.deleteEvent();
        Logger.log(event);
      }
        var title = row[1];
        var desc = row[4];
        var tstart = row[2];
        var tstop = row[3];
        var newEvent = cal.createEvent(title, tstart, tstop, {description:desc});
        var newEventID = newEvent.getId();
        EventsData.getRange(row[7]).setValue(newEventID);
        Logger.log(newEventID);
    }
  });
}

I have tried setting static values for the said getRange() line, which works: EventsData.getRange(2,8).setValue(newEventID); but that doesn't get looped.

I have tried using EventsData.row[7].setValue(newEventID); and eventID.setValue(newEventID);, which do not work.

I appreciate you looking!

Answer 1

When your showing script is modified, how about the following modification?

Modified script 1:

By this modification, the value of newEventID is put into column "H" of the processed row.

function ReplaceOrCreateEvent() {
  var cal = CalendarApp.getCalendarById("calendarID");
  var EventsData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SHEET_NAME');
  var rows = EventsData.getDataRange().getValues();
  var headers = rows.shift();
  rows.forEach(function (row, i) { // Modified
    if (row[0]) {
      Logger.log(JSON.stringify(row));
      var eventID = row[7];

      if (eventID != 0) {
        var event = cal.getEventById(eventID);
        event.deleteEvent();
        Logger.log(event);
      }
      var title = row[1];
      var desc = row[4];
      var tstart = row[2];
      var tstop = row[3];
      var newEvent = cal.createEvent(title, tstart, tstop, { description: desc });
      var newEventID = newEvent.getId();
      EventsData.getRange(i + 2, 8).setValue(newEventID); // Modified
      Logger.log(newEventID);
    }
  });
}

Modified script 2:

As another modification, in the case of the above-modified script, setValue is used in a loop. If you want to move it out of the loop to reduce the process cost, how about the following modification?

function ReplaceOrCreateEvent() {
  var cal = CalendarApp.getCalendarById("calendarID");
  var EventsData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SHEET_NAME');
  var rows = EventsData.getDataRange().getValues();
  var headers = rows.shift();
  var res = rows.map(function (row, i) { // Modified
    if (row[0]) {
      Logger.log(JSON.stringify(row));
      var eventID = row[7];

      if (eventID != 0) {
        var event = cal.getEventById(eventID);
        event.deleteEvent();
        Logger.log(event);
      }
      var title = row[1];
      var desc = row[4];
      var tstart = row[2];
      var tstop = row[3];
      var newEvent = cal.createEvent(title, tstart, tstop, { description: desc });
      var newEventID = newEvent.getId();
      Logger.log(newEventID);
      return [newEventID] // Modified
    }
    return [row[7]]; // Added
  });
  EventsData.getRange(2, 8, res.length).setValues(res); // Added
}