How to Share User Properties between Google Apps Script Projects

The Properties Service of Google Apps Script is used by developers to store app configuration and user specific settings. The properties data is scoped to a specific user, or a specific project, and cannot be shared between different projects.

The Email Form Notifications add-on also uses the Properties Service to store rules that are defined by the user. The rules created by User A are not accessible to User B.

However, in some specific cases, we may want to give access to our store data to another user so they may build upon the existing configuration rather than having to build everything from scratch.

The new import export option allows the user to export properties data as a plain text file that can be imported into the property store of another user.

Access the Property Store

On the server side (Google Script), we define two methods - one for exporting data as a JSON file and the other method for importing data from the property store of another user into our own.

/* Choose DocProperties for editor add-ons */
const getStore = () => {
  return PropertiesService.getUserProperties();
};

/* Export user settings */
const exportUserData = () => {
  const data = getStore().getProperties();
  return JSON.stringify(data);
};

/* Import user settings */
const importUserData = (data) => {
  const json = JSON.parse(data);
  getStore().setProperties(json);
  return 'OK';
};

Export User Properties as a Text File

For exporting data, the HTML file contains a simple download button that connects to the server, fetches the data and allows the user to save this data as a text file on their computer.

<p>Export Data</p>
<button onclick="downloadFile();return false;" href="#">Export</button>

<script>
  function downloadFile() {
    google.script
      .withSuccessHandler(function (data) {
        var a = document.createElement('a');
        var blob = new Blob([data], {
          type: 'text/plain',
        });
        var url = URL.createObjectURL(blob);
        a.setAttribute('href', url);
        a.setAttribute('download', 'file.txt');
        a.click();
      })
      .exportUserData();
  }
</script>

Import User Properties from a Text File

For importing data into the property store, the user can upload a text (JSON) file that contains data as key-value pairs. These files are easily readable in any text editor and you can also add define new properties by adding new keys to the JSON file.

<p>Import data</p>
<input type="file" id="file" accept="text/plain" />

<script>
  document.getElementById('file').addEventListener(
    'change',
    function (event) {
      var file = event.target.files[0];
      if (file.type !== 'text/plain') {
        window.alert('Unsupported file');
        return;
      }
      var reader = new FileReader();
      reader.onload = function (e) {
        google.script.run
          .withSuccessHandler(function (success) {
            window.alert(success);
          })
          .withFailureHandler(function (err) {
            window.alert(err);
          })
          .importUserData(e.target.result);
      };
      reader.readAsText(file);
    },
    false
  );
</script>

The File Reader API of JavaScript is used to read the contents of the selected text file. The onload event gets fired when the file has been successfully read in memory.

The readAsText method of File Reader will read the file as a string but you may also use the readAsDataURL method should be wish to upload file in base64 encoded format that can be decoded on the server.

Useful JavaScript Functions

The code snippets are from a JavaScript course on Udemy.

  1. Create a function that can be invoked only once.
const once = (fn, ...args) => {
  let called = false;
  return () => {
    if (called === false) {
      called = true;
      return fn(...args);
    }
    return 'Cannot call again';
  };
};

const printName = (text, time) => console.log(`${text} at ${time}`);
const fn = once(printName, 'Google', new Date().toString());

console.log(fn());
console.log(fn());
  1. Measure the time it takes for a JavaScript function to run.
const getUserData = async (user) => {
  const response = await fetch(`https://api.github.com/users/${user}`);
  const json = await response.json();
  return json;
};

const time = (fn, ...args) => {
  console.time('time');
  const result = fn(...args);
  console.timeEnd('time');
  return result;
};

time(() => getUserData('labnol'));
  1. A debounce function that delays invocation until a certain amount of time has passed since the last time that debounce function was invoked.
const debounce = (fn, waitInMs) => {
  let debounced = false;
  return (...args) => {
    if (debounced) clearTimeout(debounced);
    debounced = setTimeout(() => fn(...args), waitInMs);
  };
};

const getWindowLayout = (event) => {
  console.log(event, window.innerHeight, window.innerWidth);
};

window.addEventListener('resize', debounce(getWindowLayout, 500));

How to Create JSON Web Token (JWT) with Google Apps Script

You can use Google Script to create JSON Web Tokens (JWT) that can be provided to secure routes so that only authenticated requests that contain a valid token can connect to the APIs (e.g., the Zoom API).

All JSON Web Tokens have three parts:

  1. The header that specifies the hash algorithm that is used for signing and decrypting the JWT.
  2. The payload in JSON format that contains all the user data. The iat and exp properties represent the issue date and the expiration time respectively but you can pass any data to the payload.
  3. The signature data that allows APIs to establish the authenticity of the access token.

The parts are joined with a dot (period) and data is encoded in Base64 using the Utilities.base64EncodeWebSafe method of Apps Script.

Create JSON Web Token

const createJwt = ({ privateKey, expiresInHours, data = {} }) => {
  // Sign token using HMAC with SHA-256 algorithm
  const header = {
    alg: 'HS256',
    typ: 'JWT',
  };

  const now = Date.now();
  const expires = new Date(now);
  expires.setHours(expires.getHours() + expiresInHours);

  // iat = issued time, exp = expiration time
  const payload = {
    exp: Math.round(expires.getTime() / 1000),
    iat: Math.round(now / 1000),
  };

  // add user payload
  Object.keys(data).forEach(function (key) {
    payload[key] = data[key];
  });

  const base64Encode = (text, json = true) => {
    const data = json ? JSON.stringify(text) : text;
    return Utilities.base64EncodeWebSafe(data).replace(/=+$/, '');
  };

  const toSign = `${base64Encode(header)}.${base64Encode(payload)}`;
  const signatureBytes = Utilities.computeHmacSha256Signature(
    toSign,
    privateKey
  );
  const signature = base64Encode(signatureBytes, false);
  return `${toSign}.${signature}`;
};

Generate Token with your Private Key & Payload

const generateAccessToken = () => {
  // Your super secret private key
  const privateKey =
    'ZPYu33tz8QYU3hwJQXgHpZsKfYn0r2poopBx7x1n3rmeIvuGU4wf65kk6rV1DrN';
  const accessToken = createJwt({
    privateKey,
    expiresInHours: 6, // expires in 6 hours
    data: {
      iss: Session.getActiveUser().getEmail(),
      userId: 123,
      name: 'Amit Agarwal',
    },
  });
  Logger.log(accessToken);
};

You can paste the generated access token in jwt.io and you’ll be able to see the content (payload) of the decoded token. Please note that if the token has invalid signature data, the payload may still be decoded as it is encoded in Base64.

JSON Web Token with Google Apps Script

Decoding JWT Payload with Google Apps Script

const parseJwt = (jsonWebToken, privateKey) => {
  const [header, payload, signature] = jsonWebToken.split('.');
  const signatureBytes = Utilities.computeHmacSha256Signature(
    `${header}.${payload}`,
    privateKey
  );
  const validSignature = Utilities.base64EncodeWebSafe(signatureBytes);
  if (signature === validSignature.replace(/=+$/, '')) {
    const blob = Utilities.newBlob(
      Utilities.base64Decode(payload)
    ).getDataAsString();
    const { exp, ...data } = JSON.parse(blob);
    if (new Date(exp * 1000) < new Date()) {
      throw new Error('The token has expired');
    }
    Logger.log(data);
  } else {
    Logger.log('đź”´', 'Invalid Signature');
  }
};

If you are new to JWT, the video tutorials by Kyle Cook here and here are a good place to start.

Lite YouTube Embeds – A Better Method for Embedding YouTube Videos on your Website

It is easy to embed a YouTube video but you’ll be surprised to know how much extra weight a single YouTube video embed can add to your web pages. The browser has to download ~800 kB of data (see screenshot) for rendering the YouTube video player alone. And these files are downloaded even before the visitor has clicked the play button.

Embed YouTube Video Player Size

The embedded YouTube video not only increases the byte size of your web pages but the browser has to make multiple HTTP requests to render the video player. This increases the overall loading time of your page thus affecting the page speed and the core vitals score of your website.

The other drawback with the default YouTube embed code is that it renders a video player of fixed dimensions and isn’t responsive. If people view your website on a mobile phone, the video player may not resize properly for the small screen.

Embed YouTube Videos without Increasing Page Size

The now defunct Google+ employed a very clever technique for embedding YouTube videos. When the page was initially loaded, Google+ would only embed the thumbnail image of the YouTube video and the actual video player was loaded only when the user clicked inside thumbnail.

The thumbnail frame image of YouTube videos are about 15 kB in size so we are able to reduce the size of web pages by almost an MB.

Lite YouTube Embed Demo

Open this demo page to view the Google+ technique in action. The first video the page is embedded using the default IFRAME code while the second video uses the lite mode that loads the YouTube video on demand only.

When a user clicks the play button, the thumbnail image is replaced with the standard YouTube video player with autoplay set to 1 so the video would play almost instantly. The big advantage is that the extra YouTube JavaScript gets loaded only when someone decides to watch the embedded video and not otherwise.

Light and Responsive YouTube Embeds

The standard embed code for YouTube uses the IFRAME tag where the width and height of the video player are fixed thus making the player non-responsive.

The new on-demand embed code for YouTube is responsive that adjusts the player dimensions automatically based on the screen size of the visitor.

YouTube Embed Code

Embed YouTube Videos Responsively - Tutorial

Step 1: Copy-paste the following HTML snippet anywhere in your web page where you would like the YouTube video to appear. Remember to replace VIDEO_ID with the actual ID of your YouTube video.

<div class="youtube-player" data-id="VIDEO_ID"></div>

We will not assign height and width since the video player will automatically occupy the width of the parent while the height is auto-calculated. You can also paste multiple DIV blocks with different video IDs if you need to embed multiple YouTube videos on the same page.

Step 2: Copy-paste the JavaScript anywhere in your web template. The script finds all embedded videos on a web page and then replaces the DIV elements with the video thumbnails and a play button (see demo).

<script>
  /*
   * Light YouTube Embeds by @labnol
   * Credit: https://www.labnol.org/
   */

  function labnolIframe(div) {
    var iframe = document.createElement('iframe');
    iframe.setAttribute(
      'src',
      'https://www.youtube.com/embed/' + div.dataset.id + '?autoplay=1&rel=0'
    );
    iframe.setAttribute('frameborder', '0');
    iframe.setAttribute('allowfullscreen', '1');
    iframe.setAttribute(
      'allow',
      'accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture'
    );
    div.parentNode.replaceChild(iframe, div);
  }

  function initYouTubeVideos() {
    var playerElements = document.getElementsByClassName('youtube-player');
    for (var n = 0; n < playerElements.length; n++) {
      var videoId = playerElements[n].dataset.id;
      var div = document.createElement('div');
      div.setAttribute('data-id', videoId);
      var thumbNode = document.createElement('img');
      thumbNode.src = '//i.ytimg.com/vi/ID/hqdefault.jpg'.replace(
        'ID',
        videoId
      );
      div.appendChild(thumbNode);
      var playButton = document.createElement('div');
      playButton.setAttribute('class', 'play');
      div.appendChild(playButton);
      div.onclick = function () {
        labnolIframe(this);
      };
      playerElements[n].appendChild(div);
    }
  }

  document.addEventListener('DOMContentLoaded', initYouTubeVideos);
</script>

Step 3: Copy-paste the CSS before the closing head tag of your web template.

<style>
  .youtube-player {
    position: relative;
    padding-bottom: 56.25%;
    height: 0;
    overflow: hidden;
    max-width: 100%;
    background: #000;
    margin: 5px;
  }

  .youtube-player iframe {
    position: absolute;
    top: 0;
    left: 0;
    width: 100%;
    height: 100%;
    z-index: 100;
    background: transparent;
  }

  .youtube-player img {
    object-fit: cover;
    display: block;
    left: 0;
    bottom: 0;
    margin: auto;
    max-width: 100%;
    width: 100%;
    position: absolute;
    right: 0;
    top: 0;
    border: none;
    height: auto;
    cursor: pointer;
    -webkit-transition: 0.4s all;
    -moz-transition: 0.4s all;
    transition: 0.4s all;
  }

  .youtube-player img:hover {
    -webkit-filter: brightness(75%);
  }

  .youtube-player .play {
    height: 72px;
    width: 72px;
    left: 50%;
    top: 50%;
    margin-left: -36px;
    margin-top: -36px;
    position: absolute;
    background: url('//i.imgur.com/TxzC70f.png') no-repeat;
    cursor: pointer;
  }
</style>

You can view the light YouTube embed technique in action on this Codepen page.

Please do note that Chrome and Safari browsers on iPhone and Android only allow playback of HTML5 video when initiated by a user interaction. They block embedded media from automatic playback to prevent unsolicited downloads over cellular networks.

YouTube Embed Tutorials

  1. Embed a YouTube Video with Sound Muted
  2. Place YouTube Video as your Webpage Background
  3. Embed Just a Portion of a YouTube Video

JavaScript Objects Quick Reference

Any object in JavaScript is a collection of key-value pairs. The key, also known as a property, is a unique string that maps to a value which may be a Boolean, String or another object.

Let’s take a simple person object that contains properties like name, age and the employment status.

const person = {
  name: 'John',
  age: 21,
  gender: 'Male',
  employed: false,
};
  • Check if a property (or key) exists in an object
console.log('country' in person); // returns false
console.log('employed' in person); // returns true
console.log(person.hasOwnProperty('gender'));
  • Iterate over an object and print the key-value pairs
Object.keys(person).forEach((key) => {
  console.log(`${key}: ${person[key]}`);
});

Object.entries(person).forEach(([key, value]) => {
  console.log(`${key}: ${value}`);
});
  • Prevent new properties from being added to the object
Object.preventExtensions(person);
person.full_name = 'John Q Public';
console.log(person); // the full name property is not added
  • Check if new properties can be added to an object
Object.isExtensible(person);
delete person.name; // you can still delete properties
  • Prevent properties from getting added or deleted
Object.seal(person);
delete person.age;
console.log(person.age); // the property is not deleted
  • Check if properties can be added or deleted from any object
Object.isSealed(person);
  • Prevent properties from getting added, deleted or modified
Object.freeze(person);
  • Check if an object can be modified
Object.isFrozen(person);
  • Combine two objects (use default values)
const defaultPerson = {
  name: 'Unknown',
  country: 'Unknown',
};

const newPerson = {
  name: 'John',
  age: 21,
};

const mergedPerson = Object.assign(defaultPerson, newPerson);
console.log(mergedPerson);
  • Create a shallow clone of an object
const clone = Object.assign({}, person);
// changes to the clone will not modify the original object

How to Play YouTube Videos at Custom Speed

The settings pane in the YouTube video player lets you quickly change the default playback speed of the current video. You may go as high as 2x that will play the video at twice the normal speed. The lower limit is 0.25 that will slow down the video to one-fourth the original speed.

Change YouTube Video Speed

Watch YouTube at Custom Speed

YouTube allows you to play videos at 2x the original speed but what if you want to speed up and watch videos at an even higher speed - like 4x or 10x the normal speed?

That’s where Chrome Developer Tools can help.

Open any YouTube video inside Google Chrome and launch the JavaScript console from the Chrome menu bar. Go to the View menu, choose Developer, and select JavaScript console from the sub-menu.

Inside the console window, type the following command and it will instantly change the playback speed of the current video to 8x the normal speed.

$('video').playbackRate = 8;

If you want to slow down a video, try a value lower than 1 as:

$('video').playbackRate = 0.125;

You may set the playback speed to any value between 0.0625 and 16. This is the allowed range of media playback rate in Chrome.

Also see: Embed Lightweight YouTube Player

Build a COVID-19 Self Assessment Tool with Google Forms

Businesses and schools worldwide are using Google Forms to build COVID-19 self-declaration forms that employees, students and visitors must complete every day before they can attend work. Here is a sample COVID-19 Health Screening Form - if the answer is “yes” to any of the questions, the person is expected to stay home.

COVID-19 Google Form

After a respondent submits the form, a confirmation email is sent to them instantly with the Email Notifications add-on. The email is like a clearance certificate detailing whether the person can attend work or not. If they are allowed entry, the email also contains a dynamic QR Code that can be scanned and verified at the entry point.

Send Conditional Notification Emails

The conditional notifications feature of the Google Forms add-on automatically determines if the respondent should be sent the “Allowed to enter premises” email or not. It looks at the form’s answers and compares them with the specified criteria to make this choice.

COVID-19 Health Screening Questionnaire

For instance, if the employee has entered a value greater than 100.4 in the temperature field, they are sent the “Work from home” email. Similarly, they are not allowed to attend work if they selected any value other than “None of the above” for the symptoms question.

To enable this workflow with Google Forms, you are required to create two email rules - one rule for employees that have passed the self-assessment test and the other rule for people who are required to work from home based on their self-assessment.

Rule 1: Allowed to Attend Work

Create a new rule for the respondents and set the conditional notifications as shown in the screenshot.

Allowed to Attend Office

You can put {{Email Address}} in the email field and this will be replaced with the respondent’s email address that is submitted in the form entry. If you have a Google Form that is restricted to your school or organization, the email address of the submitter will be automatically recorded in the form entry.

For the email template, you can use the QR Code function that will add a dynamic image in the outgoing email with the form answers.

Email Template

Rule 2: Work from Home

To save time, duplicate the previous rule and edit the conditional notification to send a different email to people who aren’t considered fit to attend office and should continue working from home.

If you compare this conditional logic screen with the previous one, you’ll notice that it uses OR instead of AND with different criteria indicating that if either of the conditions is true, the email should be sent.

Any condition matches

Demo Check-in Google Form

If you would like to test this self-assessment tool, fill this COVID-19 Google Form and you’ll receive an instant confirmation email with the result. Here’s a copy of the email sent by the Google Form when the respondent passes the self-assessment.

Check the Form Notifications user guide to learn more about the features of the add-on.

Generate firebase.json file for Firebase Redirects

We recently moved the user guide for Mail Merge and Form Notifications from the website labnol.org to digitalinspiration.com. As with any domain move, we had to manually setup 301 redirects so that the audience are automatically redirected to the new website should they happen to click any of the links that still point to the old domain.

Because the websites are hosted on Firebase, it is easy to setup 301 redirects through the firebase.json file. All we need are entries in the redirects array, one entry per redirect, specifying the source, the destination URL and it is also possible to define if the redirect is 301 (permanent) or a temporary 302 redirect.

{
  "redirects": [
    {
      "source": "/page1",
      "destination": "https://digitalinspiration.com/page1",
      "type": 301
    },
    {
      "source": "/page2{,/**}", // also redirect pages ending with slash
      "destination": "https://digitalinspiration.com/page2",
      "type": 302
    }
  ]
}

When you are migrating big sites, it can become difficult to maintain the firebase.json file as 100s of URLs that may have to added in the redirects array. As as workaround, you can create a separate JSON file with all the redirects and then generate the firebase.json file dynamically.

The firebase file is generated automatically from the redirects file before the assets are uploaded to Firebase hosting.

Step 1: Create a base file firebase.base.json. As you can see, we have a few redirects setup already and the new redirect entries will be merged into this array.

{
  "hosting": {
    "public": "public",
    "ignore": ["firebase.json", "**/.*", "**/node_modules/**"],
    "redirects": [
      {
        "source": "/foo{,/**}",
        "destination": "/bar",
        "type": 301
      },
      {
        "source": "/firebase/**",
        "destination": "https://firebase.google.com/",
        "type": 302
      }
    ]
  }
}

Step 2: Create a firebase.redirects.json file with the links property that contains an array of links. Each link entry will have the source regex pattern or glob, the description URL and the type of redirect (optional).

{
  "links": [
    [
      "/email-google-form-responses-7263",
      "https://digitalinspiration.com/docs/form-notifications/email-multiple-people"
    ],
    [
      "/embed-qrcode-barcode-google-forms-021020",
      "https://digitalinspiration.com/docs/form-notifications/barcode-qrcode"
    ],
    [
      "/internet/google-forms-mobile-notifications/29203",
      "https://digitalinspiration.com/docs/form-notifications/phone-push-notifications",
      false
    ]
  ]
}

Step 3: Create a generate.js that will read the the base file and generate a new firebase.json file using redirects listed in the redirects.json file. All

const fs = require('fs');

const redirects = fs.readFileSync('firebase.redirects.json');
const { links = [] } = JSON.parse(redirects);

const linkMap = links.map((link) => {
  const [source, destination, permanent = true] = link;
  return {
    source: `${source}{,/**}`,
    destination,
    type: permanent ? 301 : 302,
  };
});

const firebase = fs.readFileSync('firebase.base.json');

const file = JSON.parse(firebase);

file.hosting.redirects = [...file.hosting.redirects, ...linkMap];

fs.writeFileSync('firebase.json', JSON.stringify(file, null, 2));

Step 4: Inside the package.json file, add a new entry in the script section to generate the file before the upload step.

{
  "scripts": {
    "generator": "node generate.js",
    "upload": "npm run generator && firebase deploy --only hosting"
  }
}

This will ensure that a new firebase.json file is regenerated before deployment.

How to Change the Reply-to Address in Mail Merge

When you send an email campaign through Gmail, you’ve an option to specify a different reply-to email address for your emails. When the email recipient hits the “Reply” or “Reply All” button, the To field in their email reply will be automatically populated with the email address that you’ve specified as the Reply-to email at the time of sending.

You can even specify more than one email addresses in the reply-to field, separated by commas, and they will all show up in the To field of the reply field. For instance, you could send emails from your own email address but the replies would be received in your email inbox as well as the support team.

To get started, open your Google sheet, go to the add-ons menu and choose Mail merge with attachments.

Next click on the Configure menu to open the Mail Merge sidebar.

Change Reply-to Email address

Here go the Reply-to address file and type an email address. If you wish to receive replies on multiple email addresses, type them all here separated by commas.

Now when you send the email campaign, open one of the emails in the sent items folder, expand the message header and you should see the specified email addresses listed in the reply-to field.

Different Reply-to address

Why is Google Ignoring the Reply-to Address

If you send a test email to yourself, you’ll get the email in your inbox. If you hit the reply button in that message, you may notice that that reply-to field contains your own email address and not the custom email address(es) that you’ve specified in your mail merge.

That’s the default behavior in Gmail if the “from” address on an email message is the same as the “to” address or is one of your own email aliases. To test your reply-to functionality, you should send emails to an email address that is not connected to your current Gmail account or set the “From” address as a non-Gmail address.

Download Gmail Messages as EML Files in Google Drive

This Google Script will help you download your email messages from Gmail to your Google Drive in the EML format.

What is the EML Format

The .eml file format is popular for transferring emails from one email program to another since it complies with the RFC 822 standard and thus can be natively opened inside Apple Mail and Microsoft Outlook. You can even open EML files inside Google Chrome by dragging the file from your desktop onto a new browser table.

EML files contains the email content (email body, header and encoded images and attachments) as plain text in MIME format.

Download Gmail message as EML Files

Inside Gmail, open any email thread, go to the 3-dot menu and choose “Download Message” from the menu. It will turn your current email message into an eml file and save it your desktop.

However, if you wish to automate the process and download multiple emails as eml files in your Google Drive, Apps Script can help.

const downloadEmails = () => {
  const sender = 'sender@domain.com';
  const threads = GmailApp.search(`from:${sender}`).slice(0, 10);
  threads.forEach((thread) => {
    const subject = thread.getFirstMessageSubject();
    const [message] = thread.getMessages();
    const rawContent = message.getRawContent();
    const blob = Utilities.newBlob(rawContent, null, `${subject}.eml`);
    const file = DriveApp.createFile(blob);
    Logger.log(subject, file.getUrl());
  });
};

The script searches for emails from the specified sender, gets the first email message and downloads it your Google Drive.

Forward Gmail as EML Attachment

If you are to forward an email message as an attachment, the .eml format may be recommended since it preserves all the formatting and attachments of the original email thread in a single file that can be attached to the email.

const forwardEmail = () => {
  const messageId = '123';
  const message = GmailApp.getMessageById(messageId);
  const rawContent = message.getRawContent();
  const blob = Utilities.newBlob(rawContent, null, `email.eml`);
  GmailApp.sendEmail('to@gmail.com', 'This email contains an eml file', '', {
    attachments: [blob],
  });
};

Also see: Download Gmail as PDF Files