Home>

As an in-house tool, you can register the schedule by filling in the google form.

To be precise, in the flow of google form → spreadsheet → google calendar, the part to be registered in google calendar from the spreadsheet is described in gas, and the trigger is set when the form is submitted.
I have confirmed the operation, but there are some cases where it is not reflected in the calendar even if I submit the form.

・ Since it is reflected in the spreadsheet, the form can be submitted without fail.
・ Even if it fails once, it will be registered if you resend it with the same content.

From the above situation
1, Communication environment problems
2, code problem
I think it is either. I think that 1 is more likely, because it is a reinforced concrete building, it is difficult for radio waves to reach, and the in-house wifi is unstable depending on the location.

I'd like to ask you if the code content is really okay and if there are other possibilities.

↓ Code

function createEvent () {
  var calendar = CalendarApp.getCalendarById ("ID");
  var spreadsheet = SpreadsheetApp.openById ("ID");
  var scheduleSheet = spreadsheet.getSheetByName ("Schedule");
  var lastRow = scheduleSheet.getLastRow ();
  var title = scheduleSheet.getRange (lastRow, 2) .getValue ();
  var startTime = scheduleSheet.getRange (lastRow, 3) .getValue ();
  var endTime = scheduleSheet.getRange (lastRow, 4);
  var description = scheduleSheet.getRange (lastRow, 6);
  var options = {description: description.getValue ()}
  if (description.isBlank ()&&startTime<endTime.getValue ()) {
    calendar.createEvent (title, startTime, endTime.getValue ());
  } else if (description.isBlank ()&&endTime.isBlank ()) {
    calendar.createAllDayEvent (title, new Date (startTime.getFullYear (), startTime.getMonth (), startTime.getDate (), 0,0,0));
  } else if (endTime.isBlank ()) {
    calendar.createAllDayEvent (title, new Date (startTime.getFullYear (), startTime.getMonth (), startTime.getDate (), 0,0,0), options);
  } else {
    calendar.createEvent (title, startTime, endTime, options)
}
}


]


(Partially painted black)

Thank you.

Postscript
· This script is on the form

  • Answer # 1

    If you set it as a trigger for the form, the script may have been executed before the value was written to the spreadsheet, so you need to get the submitted value from the submitted content of the form.

    I think it can be executed with a script like the one below.

    function createEvent (e) {
      const calendar = CalendarApp.getCalendarById ("ID");
      const res = {};
      const itemResponse = e.response.getItemResponses ();
      itemResponse.forEach (response =>{
        const title = response.getItem (). getTitle ();
        const value = response.getResponse ();
        switch (title) {
          case'name of appointment':
            res.title = value;
            break;
          case'start date and time':
            res.startTime = value;
            break;
          case'End date and time (optional)':
            res.endTime = value;
            break;
          case'Appointment details (optional)':
            res.description = value;
            break;
        }
      });
      const options = res.description? {description: res.description}: {};
      if (res.endTime) {
        calendar.createEvent (res.title, res.startTime, res.endTime, options);
      } else {
        calendar.createAllDayEvent (res.title, new Date (res.startTime.getFullYear (), res.startTime.getMonth (), res.startTime.getDate (), 0,0,0), options);
      }
    }

  • Answer # 2

    Triggering when submitting a form is not recommended if wifi is unstable.
    Even if you can write to SS from the form, there is a possibility that the process of registering the schedule in the calendar is unstable and it ends in the middle.
    As a countermeasure
    -Process at intervals of 5 minutes instead of submitting the form.
    -Add an item of calendar registration status to the answer sheet,
    If it is blank, create a logic that flags the record as "done" when it is completed by executing the process of registering the record in the calendar.

    By doing so, regardless of wifi instability, it will check the status of the flag every 5 minutes and process it.

Related articles