0

I am trying to write the Script to turn single rows of a Spreadsheet into a Google Calendar event and invite the emails in one of the columns to the event. This will be used in a school setting where students are signing up for workshops and I want to add it to their Google Calendar.

Link to Demo Spreadsheet

Here is the code I've been working with from another forum on here. It hasn't been able to send the invite/add to the other calendar.

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var values = ss.getDataRange().getDisplayValues()
  var lc = ss.getLastColumn()
  for (var i = 1; i < values.length; i++) {
    var evId = processLine(values[i], lc) // create the event and get id of the event
    ss.getRange(i, lc).setValue(evId)     // write the id of the event in the last column of the sheet
  }
}

function processLine(x, lc) {
  var jobType = x[5]
  var evId = x[lc-1]

  // check job type
  if (jobType == 1 && evId == '') {
    // summary
    // description
    var freq = x[6]
    var description = type + ' - ' + dur + ' - ' + freq
    // location
    var location = x[10]
    // date
    var start = x[8]
    var end = x[9]
    var date = x[7]
    // formated date
    var eStart = new Date(date + ' ' + start)
    var eEnd = new Date(date + ' ' + end)
    // options
    var opts = {
      location: location
    }

    // SINGLE EVENTS
    if (freq == 'Once Off') {
      var ev = CalendarApp.createEvent(summary, eStart, eEnd, opts)
    } else {

// CREATE EVENT
      var ev = CalendarApp.createEventSeries(summary, eStart, eEnd, recurrence, opts)
    }
    // ADD PARAMETERS
    ev.setColor(CalendarApp.EventColor.YELLOW)
    ev.setDescription(description)
    console.log(ev.getId())
    return ev.getId()

  }
}
pres10
  • 19
  • 7
  • Can you provide a brief explanation of how `Session choice`, `Block number`, `Frequency`, and `Location` will be used in the event creation? Sharing the form could also be useful so that we can see the kind of data you will be using for those values. – Fernando Lara Jul 08 '22 at 23:52
  • 1- when the form is submitted you should use onFormSubmit, 2- processLine is not suitable here and should be rewritten in the simplest way – Mike Steelson Jul 09 '22 at 04:08
  • 3- to avoid duplicates in your own calendar, you must first create an event for each session, then automatically add guests using onFormSubmit – Mike Steelson Jul 09 '22 at 09:55
  • do you need any more assistance? – Mike Steelson Jul 19 '22 at 05:28
  • Yep, still unsolvable. – pres10 Sep 15 '22 at 02:58
  • If it's still actual for you, here's code that adds a guest to an existing event: https://stackoverflow.com/a/75165818/1729543 I believe you can extend it to create an event with CalendarAPI – Slava Medvediev Jan 18 '23 at 22:17

0 Answers0