Question 335753 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Write Calendar Event ID on Google Sheets with CalendarApp on Google Sheets Spreadsheet

I am writing an App Scripts code to write events from my spreadsheet into my Google Calendar, but I want to save the Event ID on the right of the range. I can get the event id no problem, but I don’t know how to write it to the column H since I am using a forEach(entry) loop.

This is my current js code:

// Get calendar id from calendar settings
  calendar_id = [calendar id]
  // Create your Calendar App connection
  let myCalendar = CalendarApp.getCalendarById(calendar_id);
  // Get current sheet
  let sheet = SpreadsheetApp.getActiveSheet();
  // Get data range and values from current sheet
  let schedule = sheet.getDataRange().getValues()
  schedule.splice(0, 1)

  // Create events from each row in the sheet
  schedule.forEach(function(entry) {
    task_name = entry[0]
    task_start = new Date(entry[1])
    task_end = new Date(entry[2])
    guest_list = entry[6]
    event = myCalendar.createEvent(task_name, task_start, task_end, {guests: guest_list});
    event_id = event.getId();
    // How do I make it so that the event id will be written on what would be 'entry[7]'?
  });
}

How do I add the id to the H column?

This is the format of my spreadsheet:
enter image description here

Tags:

2

Answers


  1. 0

    Use an accumulating array to build the entire column of values, and place all of it into the sheet at the end with setValues():

    // Get calendar id from calendar settings
  calendar_id = [calendar id]
  // Create your Calendar App connection
  let myCalendar = CalendarApp.getCalendarById(calendar_id);
  // Get current sheet
  let sheet = SpreadsheetApp.getActiveSheet();
  // Get data range and values from current sheet
  let schedule = sheet.getDataRange().getValues()
  schedule.splice(0, 1)

  let eid = [];

  // Create events from each row in the sheet
  schedule.forEach(function(entry) {
    task_name = entry[0]
    task_start = new Date(entry[1])
    task_end = new Date(entry[2])
    guest_list = entry[6]
    event = myCalendar.createEvent(task_name, task_start, task_end, {guests: guest_list});
    event_id = event.getId();
    // How do I make it so that the event id will be written on what would be 'entry[7]'?
    eid.push([event_id]);
  });
  sheet.getRange(2,8,eid.length,1).setValues(eid);
}
    Login or Signup to reply.
  2. 0 
    function myFunk() {
  let myCalendar = CalendarApp.getCalendarById("calendar_id");
  let sh = SpreadsheetApp.getActiveSheet();
  let vs = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn()).getValues();
  let col8 = vs.map(r => {
    task_name = r[0]
    task_start = new Date(r[1])
    task_end = new Date(r[2])
    guest_list = r[6]
    event = myCalendar.createEvent(task_name, task_start, task_end, { guests: guest_list });
    event_id = event.getId();
    return [eveht_id];
  });
  sh.getRange(2, 8, col8.length, col8[0].length).setValues(col8)
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search