Jennie

Posted on Feb 1, 2021 • Updated on Feb 12, 2021

Saving time from tedious regulars by automating with Google Apps Script

#googlecloud #automation #appsscript #sheet

Regular team sharing is a great culture. Yet for the organiser, it is a tedious work reduces the precious coding time.

Let's assume one team sharing cycle works like this:

Shouting out in the team message channel (like slack) and asking for presenters, repeat X times before a cutting-off time.
When a presenter registers, a friendly communication repeating questions like "What is the topic?", "How much time estimated?", etc.
Confirm the agenda and announce the team.
Host the sharing session.
Collect feedback from the audience after the session, and forward to the presenters.

The "repeat" word appears 2 times in the above cycle, and the cycle itself repeats.

That's tedious! But come on! We are programmers. We suppose to resolve "repeat" tedious works with our favourite code!

Analysing what is possible

First of all, let's investigate and explore what we can do before jumping in the code.

Sending message

Messaging tools like Slack has introduced webhooks to help with 3rd party integration. And the webhook is a dynamically created HTTP API that user could send an HTTP message following the required format to do something on the APP.

Which means, we could simply create an API following the APP instruction and fetch() it to send a message:

fetch('https://hooks.slack.com/services/T00000000/B00000000/XXXXXXXXXXXXXXXXXXXXXXXX', {
  method: 'POST',
  headers: {
    'Content-type': 'application/json'
  },
  body: JSON.stringify({
    text: "Hello, world."
  })
});

Collecting registration information and scheduling

Speaking about collecting information, Google form is absolutely an awesome choice that anyone could create and use in a few steps.

But it could only help generate simple statistics, how about something contains more steps like meeting scheduling?

Yes, it's doable with the help of Google Apps Script:

From the image above, we can feel how powerful it is. It works with all the Google stuff!

The Apps Script editor looks like this:

It shall contain at least a .gs file, and function in this .gs file to run. In one file, we could create multiple functions with any name and trigger any of them independently. Some of the function names will be used in a specific case, e.g. doGet(), doPost(), etc..

In the .gs file, I can use most of the Javascript APIs, except fetch. I need to use UrlFetchApp.fetch instead of fetch to request any external APIs, and some parameters are different.

Creating an Apps Script

2 places could create an Apps Script:

From Google Drive directly

From a file like Google Sheet

The 2nd choice is binding the script to a file, which results in reading the file content easier without knowing it's identity.

Reading a form response from Apps Script

To read a form response from Apps Script, it's better to create a linked sheet and bind the Apps Script.

The first row of the sheet will be the form questions, and each response takes one row behind sorted by the response time.

Then we could read the sheet data in the Apps Script like this:

// Get the sheet:
const sheet = SpreadsheetApp.getActiveSheet();

// Get all values:
const sheetValues = sheet.getDataRange().getValues();

// Get the value of the 1st cell:
const cell = sheetValues[0][0];

// Set a value of the 1st cell:
sheet.getRange(1, 1).setValue(‘new value’);

More APIs are available here.

Attaching extra data to the response

To help to schedule, we will need to add a "mark" to registration if we would like to keep all the previous records for references.

The "mark" could be the date of the event, which means we need to add extra data to the sheet. Will it affect the form responses?

The answer is no. It appears to have a one-way syncing relationship between the "linked" form and sheet. Since I created the sheet from the form, it is syncing from the form responses to the sheet.

If I add an extra column to the right of the sheet, nothing changes to the form.

If I add an extra row below the last row N, interestingly, the new response coming in will still be inserted below N, not the manually inserted N + 1, and pushing the manually inserted row down.

Calculate schedule

Alright, now as the concept clear, I could let my Javascript do the calculation like finding out my agenda in the Apps Script:

const SCHEDULE_COL = 4;  // Starting from 0
const sheet = SpreadsheetApp.getActiveSheet();

function getUnscheduled(date) {
  const sheetValues = sheet.getDataRange().getValues();
  const lastRow = sheet.getLastRow();
  const notScheduled = [];
  for (let i = lastRow; i--;) {
    const row = sheetValues[i];
    if (row[SCHEDULE_COL]) break;
    notScheduled.unshift(row);
  }
  return notScheduled;
}

The above script only finds out the rows without a schedule yet. But we could do one step further, to find out the sharings could fit into the event:

const SCHEDULE_COL = 4;  // Starting from 0
const DURATION_COL = 3;
const SESSION_DURATION = 60;  // In minutes
const sheet = SpreadsheetApp.getActiveSheet();

function getUnscheduled(date) {
  const sheetValues = sheet.getDataRange().getValues();
  const lastRow = sheet.getLastRow();
  const notScheduled = [];
  for (let i = lastRow; i--;) {
    const row = sheetValues[i];
    if (row[SCHEDULE_COL]) break;
    notScheduled.unshift(row);
  }
  let scheduledTime = 0;
  const safeSchedule = notScheduled.splice(
    0,
    notScheduled.findIndex(row => {
      const duration = +row[DURATION_COL];  // Make sure it is converted to number
      if (scheduledTime + duration > SESSION_DURATION) {
        return true
      } else {
        scheduledTime += duration;
      }
    )
  });
  return {
    safeSchedule,
    waitingList: notScheduled
  };
}

Furthermore, we could add some buffer time in the calculation to avoid someone taking extra time to finish or leaving some space for the Q&A session.

Generating announcement and feedback form

Once we have the agenda, then we could use them to:

Generate an agenda message and send to the team
Generate a feedback form, and send to the team

Since we've discussed sending a message via the webhook above, all we need is just a nice message format. And how to generate a form?

It's doable with Apps Script forms service.

The official document is pretty clear about how to configure each form fields.

Once the form is ready, we could get the URL to send to the team via:

const longUrl = form.getPublishedUrl();
const shortUrl = form.shortenFormUrl(longUrl);

If we want to move the form to somewhere easier to find, we could do:

DriveApp.getFileById(form.getId()).moveTo(DriveApp.getFolderById('xxx'))

The folder ID is right on your URL https://drive.google.com/drive/u/0/folders/<folder_id>.

Triggers

Every part seems ready, and I need to trigger the code.

Obviously, most of the functions are time-based, like:

Few days before the team sharing, send the reminder message.
On the day of (or a few days before) the sharing, confirm the schedule and send the schedule announcement.
Right after the team sharing, create the feedback form and send the form link.

These could be triggered on a fixed time.

While the other part of the process may require little manual effort to allow me to double confirm the information, or adapting to the ad-hoc changes. Like:

Before making the announcement, I may need to confirm that registered speakers are ready for the sharing, and adjust the schedule if necessary.
If a sharing took too much time, I may need to postpone the last sharing and adjust the feedback form.

Triggering by time

To trigger a function on a certain time, we could use Apps Script Trigger, and choose Time-driven in the configuration form:

And we could select to run at a specific time or a repeating time:

If any of these are not able to fulfil our requirement, we could dynamically create it like this:

Triggering by manual operations

When the Apps Script is deployed as a web app, its URL could serve a GET request with doGet() function, POST request with doPost() function.

The deployed URL is fixed as https://script.google.com/a/macros/[<organisation>/]s/<id>/exec.

It could response with text:

function doGet() { 
  return ContentService.createTextOutput('Hello, world!');
}

And could also response with HTML:

function doGet() { 
  return HtmlService.createHtmlOutput(
    `<a target="_blank" href="${getAnnounceScheduleLink(schedule)}">ANNOUNCE NOW</a>`
  );
}

We could find the parameters like the query parameters for the GET request in the request argument:

function doGet(request) { 
  const { action, date } = request.parameter;
  if (action === 'announce') {
    sendAnnouncement(date);
  }
  return ContentService.createTextOutput('Done!');
}

So, we could send a link like https://script.google.com/a/macros/[<organisation>/]s/<id>/exec?action=announce&date=2020-02-01 to my message channel, if I click it, the script will receive a GET request and proceed the action.

Another interesting alternative choice is sending Email with the Gmail service:

GmailApp.sendEmail(
  EMAIL_RECEIVER,
  'Email title',
  '',  // text body, if htmlBody exists, this will be ignored
  {
    htmlBody: `<a href="https://script.google.com/a/macros/[<organisation>/]s/<id>/exec?action=send_feedback_form&form=${formUrl}">SEND FEEDBACK FORM</a>`
  }
)

Debugging the script

One of the potential headache with the Apps Script is debugging. Although the script editor provides the functionality of "Run" and "Debug" from a single function, and we could even set breakpoints when running "Debug" mode, I found:

I have no idea whether the service API works as I expected
I could not enter the function arguments either with "Debug" or "Run"

To help with this, we could:

Create a group of testing functions with empty input and calling the function with the test data and debug the result.
Use Logger.log() function to log down the important parameters and steps. And these logs could be found in the editor:

Or the "Execution log" panel:

Putting together

Let me assume our sharing happens on every Friday afternoon and recap the whole design:

Create an incoming webhook for the team message channel;
Create a register form, open the response sheet, and click "Tools" > "APPs script";
Create sendMessage() to request this incoming webhook;
Create sendRegistrationReminder() to call sendMessage() with the registration form URL, and trigger it on Tuesday, Thursday at 11am;
Create sendSchedule() to call sendMessage() with the calculated schedule, and action links like "announce schedule" on Friday morning;
Create createFeedbackForm() to create the feedback form according to the schedule, and send the form URL to me during the event;
Create doGet() to handle manual actions like "announce schedule", "send feedback form";

DEV Community