Search code examples
htmlgoogle-apps-scriptgoogle-sheets-api

Generating a .ics Meeting Invitation Via Google Sheets

I'm putting together a sidebar through google sheets API that takes user input which sends indicated recipients a meeting invitation. The user input data is added to a map eventData within function sendEmail(). When I select the button 'Send Email' in my side bar, nothing happens, and I'm struggling to understand why.

Here is a pertinent snippet of my .gs code:

function generateICSContent(eventData) {
  var icsContent = 
    "BEGIN:VCALENDAR\n" + 
    "PRODID:-//Your Organization//Your App//EN\n" +
    "VERSION:2.0\n" + 
    "METHOD:REQUEST\n" +
    "BEGIN:VEVENT\n" +
    "UID:" + Utilities.getUuid() + "\n" + 
    "SUMMARY:" + eventData.get("title_subject") + "\n" +
    "DTSTART:" + eventData.get("startDate") + "T" + eventData.get("startTime") + "Z\n" + 
    "DTEND:" + eventData.get("endDate") + "T" + eventData.get("endTime") + "Z\n" + 
    "LOCATION:" + eventData.get("location") + "\n" +
    "DESCRIPTION:" + eventData.get("description") + "\n" + 
    "END:VEVENT\n" + 
    "END:VCALENDAR";

  return icsContent;
}

function sendEmail(eventData) {
  SpreadsheetApp.getActive().toast("Sending email to " + eventData.get("email"));
  var icsContent = generateICSContent(eventData);
  var attachmentBlob = Utilities.newBlob(icsContent, "text/calendar", "meeting.ics");
  MailApp.sendEmail(eventData.get("email"), eventData.get("title_subject"), eventData.get("body"), {
    attachments: [attachmentBlob]
  });
}

Here is my html:

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<script>
 function sendEmail() {
    var eventData = new Map();

    eventData.set("email", document.getElementById('email').value);
    eventData.set("title_subject", document.getElementById('title_subject').value);
    eventData.set("startDate", document.getElementById('startDate').value);
    eventData.set("endDate", document.getElementById('endDate').value);
    eventData.set("startTime", document.getElementById('startTime').value);
    eventData.set("endTime", document.getElementById('endTime').value);
    eventData.set("location", document.getElementById('location').value);
    eventData.set("body", document.getElementById('body').value);

    google.script.run.sendEmail(eventData);
 }
</script>
<body>
  Emails:
    <input type="text" id="email">
  <br>
  Subject/Meeting Title:
    <input type="text" id="title_subject">
  <br>
  Meeting Start Date:
    <input type="text" id="startDate">
  <br>
  Meeting End Date:
    <input type="text" id="endDate">
  <br>
  Meeting Start Time:
    <input type="text" id="startTime">
  <br>
  Meeting End Time:
    <input type="text" id="endTime">
  <br>
  Meeting Location:
    <input type="text" id="location">
  <br>
  Description:
    <input type="text" id="body">
  <br>
    <input type="button" class="button" value="Send Email" onclick="sendEmail();">
</body>
</html>

UPDATE: I have my sidebar widget working nicely now. It creates a .ics attachment that can be sent to indicated recipients with the option to include a zoom meeting ID. It also populates title and description info based on the active cell and the surrounding contents separated by a blank row. Here is a link to an example sheet for anyone interested:

https://docs.google.com/spreadsheets/d/1gQ_9xGudV2YboTmytJcDbOPBHb9dUUlRJPjpioF1oJQ/edit?usp=sharing

enter image description here

Solution

  • When I saw your showing script, it seems that the Map object is returned using google.script.run. I thought that this might be the reason for your current issue. In this case, how about the following modification?

    Javascript side:

    From:

    google.script.run.sendEmail(eventData);

    To:

    google.script.run.sendEmail([...eventData]);

    Google Apps Script side:

    From:

    function sendEmail(eventData) {
  SpreadsheetApp.getActive().toast("Sending email to " + eventData.get("email"));

    To:

    function sendEmail(eventData) {
  eventData = new Map(eventData);
  SpreadsheetApp.getActive().toast("Sending email to " + eventData.get("email"));

    Note:

    The modified whole script is as follows.

    HTML

    <!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<script>
function sendEmail() {
    var eventData = new Map();

    eventData.set("email", document.getElementById('email').value);
    eventData.set("title_subject", document.getElementById('title_subject').value);
    eventData.set("startDate", document.getElementById('startDate').value);
    eventData.set("endDate", document.getElementById('endDate').value);
    eventData.set("startTime", document.getElementById('startTime').value);
    eventData.set("endTime", document.getElementById('endTime').value);
    eventData.set("location", document.getElementById('location').value);
    eventData.set("body", document.getElementById('body').value);

    google.script.run.sendEmail([...eventData]);
}
</script>
<body>
  Emails:
    <input type="text" id="email">
  <br>
  Subject/Meeting Title:
    <input type="text" id="title_subject">
  <br>
  Meeting Start Date:
    <input type="text" id="startDate">
  <br>
  Meeting End Date:
    <input type="text" id="endDate">
  <br>
  Meeting Start Time:
    <input type="text" id="startTime">
  <br>
  Meeting End Time:
    <input type="text" id="endTime">
  <br>
  Meeting Location:
    <input type="text" id="location">
  <br>
  Description:
    <input type="text" id="body">
  <br>
    <input type="button" class="button" value="Send Email" onclick="sendEmail();">
</body>
</html>

    Google Apps Script:

    function generateICSContent(eventData) {
  var icsContent =
    "BEGIN:VCALENDAR\n" +
    "PRODID:-//Your Organization//Your App//EN\n" +
    "VERSION:2.0\n" +
    "METHOD:REQUEST\n" +
    "BEGIN:VEVENT\n" +
    "UID:" + Utilities.getUuid() + "\n" +
    "SUMMARY:" + eventData.get("title_subject") + "\n" +
    "DTSTART:" + eventData.get("startDate") + "T" + eventData.get("startTime") + "Z\n" +
    "DTEND:" + eventData.get("endDate") + "T" + eventData.get("endTime") + "Z\n" +
    "LOCATION:" + eventData.get("location") + "\n" +
    "DESCRIPTION:" + eventData.get("description") + "\n" +
    "END:VEVENT\n" +
    "END:VCALENDAR";

  return icsContent;
}

function sendEmail(eventData) {
  eventData = new Map(eventData);
  SpreadsheetApp.getActive().toast("Sending email to " + eventData.get("email"));
  var icsContent = generateICSContent(eventData);
  var attachmentBlob = Utilities.newBlob(icsContent, "text/calendar", "meeting.ics");
  MailApp.sendEmail(eventData.get("email"), eventData.get("title_subject"), eventData.get("body"), {
    attachments: [attachmentBlob]
  });
}

    Reference: