AppsScriptPulse

[Fixed] Google Apps Script Web App HTML form file-input fields not in blob compatible format

Image by Tumisu from Pixabay

HTML form file-input fields are not converted to compatible blob format when submitted. They are left in application/octet-stream format. The file-input fields are passed as application/octet-stream, so DriveApp.createFile(formBlob) does not work correctly … So the functionality provided by HtmlService is not consistent with the documentation

There was an annoying little Google Apps Script issue which meant when handling file uploads to published Web Apps some additional coding was required rather than just sending the file blob server side. Thankfully the open issue ticket for this has recently been marked and confirmed fixed.

Source: HTML form file-input fields not in blob compatible format

2-Way sync between sheets without a unique ID

How to setup 2-way sync within the same workbook without any unique ID.

I’m in the habit of adding IDs to sheets where I need to do lookups, but sometimes, I inherit a sheet without IDs in place (or I can’t edit the sheet structure). Spencer outlines a clever way to handle syncing between two sheets without using IDs in this video.

Source: 2-Way Sync: No Unique ID

Pseudo OnEdit Trigger for Google Document using Google Apps Script

In the current stage, there is not OnEdit trigger for Google Document. But I sometimes have the case that I want to use OnEdit trigger. So, as the current workaround, I created this sample script. I think that this workaround can be also used for Google Slides, Google Form and also Google Spreadsheet. In the case of Spreadsheet, what the existing OnEdit trigger and the time-driven trigger cannot do can be achieved.

Source: Pseudo OnEdit Trigger for Google Document using Google Apps Script

How to Automatically Add Teachable Students to Google Drive Files and Folders when they Enroll in your Course – Yagisanatode

Learn how to instantly share newly enrolled teachable students to Google Drive files and folders with Google Sheets and Apps Script.

Scott ‘Yagi’ Donald provides a very thorough walk-through of how he has connected a webhook from a third party service to Google Sheets to share specific Google Drive folders and files with named users. This post is well worth a visit as it contains a number of tips and tricks like using Google Forms to follow-up with user email addresses that are not associated with a Google account.

Source: How to Automatically Share Teachable Students to Google Drive Files and Folders when they Enroll in your Course – Yagisanatode

Adding Members to a mailing list using an API, Google Forms and Google Apps Script

Image credit: Baba Funke

I decided to explore something similar to a Webhook for Google Forms. In other words, something that triggers another action when a Google form is submitted. In this case, when a Google form is filled and submitted, the results should be sent to an API endpoint for adding members to a database. I found an answer in Google Apps Script, a coding language based on Javascript which allows for programmatically extending Google Apps like Google Forms, Sheets, Docs etc.

A nice explanation of how triggers can be used in Google Forms to push new data to other systems. The post is easy to read and includes lots of screenshots to help prevent you getting lost.

Source: Adding Members to a mailing list using an API, Google Forms and Google Apps Script

How to Fetch Employee Data from Workday by Building the Great Deku Tree Inside of Google Sheets

They say you only live once, and, with that, you may only once find yourself at a company with a video-game-centric culture. Life at Twitch creates a unique opportunity to bring the magic of gaming to your workday, even when you’re in esoteric corners of the work world such as People Analytics.

Ever wondered how people reporting was handled at Twitch? Max Brawer, Head of People Analytics at Twitch, brings his ‘A’ game outlining how a little Google Apps Script and some Workday API wrangling can be used for some fun reporting. You can read more in the source link and here’s Max’s old post on The Endless Possibilities of Freeing Your Data from Workday and Putting it into Google Sheets: a How-to.

Source: How to Fetch Employee Data from Workday by Building the Great Deku Tree Inside of Google Sheets

How to get Google Lighthouse reports using PageSpeedInsights API and Google Apps Script

Lighhouse

Image: CC-BY Google

Lighthouse is an open-source, automated tool for improving the quality of web pages. You can run it against any web page, public or requiring authentication. It has audits for performance, accessibility, progressive web apps, SEO and more.

On StackOverflow there is a nice question/answer from Mert Dökümcü on How to send Google Lighthouse reports to Slack using PageSpeedInsights API and Google Apps Script. You can read the full solution there and below is an excerpt from Mert’s post (copied here CC-BY-SA Mert Dökümcü) of the bit that really interested me, getting results from PageSpeedInsights .

First and foremost, get…

Once you have these, go to https://script.google.com/home and create a new script. The following code should do the trick:

var mobileData = fetchDataFromPSI('mobile');
var desktopData = fetchDataFromPSI('desktop');
function pageSpeedApiEndpointUrl(strategy) {
const apiBaseUrl = 'https://www.googleapis.com/pagespeedonline/v5/runPagespeed';
const websiteHomepageUrl = ''; // Your website
const apikey = ''; // Your API key
const apiEndpointUrl = apiBaseUrl + '?url=' + websiteHomepageUrl + '&key=' + apikey + '&strategy=' + strategy;
return apiEndpointUrl;
}
function fetchDataFromPSI(strategy) {
const pageSpeedEndpointUrl = pageSpeedApiEndpointUrl(strategy);
const response = UrlFetchApp.fetch(pageSpeedEndpointUrl);
const json = response.getContentText();
const parsedJson = JSON.parse(json);
const lighthouse = parsedJson['lighthouseResult']
const originLoadingExperience = parsedJson['originLoadingExperience']
const result = {
'overall_performance': originLoadingExperience['overall_category'],
'score': lighthouse['categories']['performance']['score']*100,
'firstContentfulPaint': lighthouse['audits']['first-contentful-paint']['displayValue'],
'speedIndex': lighthouse['audits']['speed-index']['displayValue'],
'timeToInteractive': lighthouse['audits']['interactive']['displayValue'],
'firstMeaningfulPaint': lighthouse['audits']['first-meaningful-paint']['displayValue'],
}
return result; 
}

Checking Google Groups membership recursively using Google Apps Script

Learn how to check indirect membership of users or groups in the context of the Google Groups Service using Apps Script.

This is a great post if you want to learn about how to programmatically navigate the hierarchy of Google Groups, which may also come in handy when you are processing other tree like structures often found in JSON. All the code needed to run this is provided and clearly explained.

Source: Getting Google Groups membership recursively using Apps Script

The Gift of Script: Convert Google Doc to PDF in a given folder

Convert a Google Doc into a PDF in a given folder and optionally delete the original Doc file.

The following Google Apps Script is designed to create a PDF file of a Google Doc in a Drive folder that you specify, with the option to delete the original Doc. This snippet of code is from larger developed solutions and allows you to understand and replicate the process.

This post is somewhat of an updated version of this Convert Doc to PDF and move into a new folder blog post. Whilst the code in it still works it was part of my early days of learning Apps Script and it was also when Google allowed for files to exist in more than one place (a feature now replaced by Shortcuts).

Source: The Gift of Script: Convert Google Doc to PDF in a given folder

Letting users run a Google Apps Script on Google Sheets without authorizing scopes and showing the source code

This is a sample workaround for letting users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script.

The post highlights the use of the existing =IMPORTML() Google Sheets function and a publish webapp to run a Google Apps Script without the end user having to authorize the script. A limitation of this approach, and also important warning to keep in mind, is the deployed web app needs to be deployed to run as the script owner and by anyone who has the web app link.

Source: Letting Users Running Google Apps Script on Google Spreadsheet without both Authorizing Scopes and Showing Script

How to Write Google Apps Script Code Locally in VS Code and Deploy It With clasp

Google Apps Script Power Up

Why Write GAS Code Locally?

When it came out, the new Google Apps Script IDE was, of course, a big deal. It’s way better than the legacy one. It brought syntax highlighting, keyboard shortcuts, command palette and just a much better UI experience.

In fact the reason it’s so good is that it’s built on top of VS Code Monaco editor. But it’s definitely still far away from what a real VS Code installation can do. You can get all the functionality of the online IDE and much more more: autocomplete, custom themes, installation of modules, linting, snippets, etc.

The bare minimum that you would need is :

  • A VS Code installation (duh!)
  • Node.js + NPM : a JavaScript runtime with a built-in module installer;
  • Autocomplete;
  • clasp: a CLI utility to sync your GAS code

Let’s get into it!

Source: How to Write Google Apps Script Code Locally in VS Code and Deploy It With clasp

How to Delete Blank Rows from Tables in Google Documents – Digital Inspiration

How to remove all blank rows from one or more tables in a Google Docs document with Google Apps Script. You may also delete blank rows from tables in Google Slides.

Source: How to Delete Blank Rows from Tables in your Google Documents – Digital Inspiration

Keep a Journal of Special Moments using Twilio Programmable SMS and Google Sheets

Learn how to build a Google Sheet journal that accepts new entries via text message. My toddler recently turned two and a half and my second daughter was born three weeks ago, so special moments are abundant but the ability to remember them is severely compromised. His suggestion of recording a memory every day in an Excel sheet or leather bound journal resonated with me, but after a few days of trying to find time to open up Excel and write a memory I realized I needed a more lightweight solution. … Twilio makes it easy and cheap to set up a dedicated phone number that can record those text messages to a Google sheet.

Nice example on the Twilio blog on using their service with Google Sheets. There is little setup required but this can all be achieved in 4 lines of code. Unfortunately, sending/receiving SMS messages with Twilio is not free, but it might be a service you keep in mind for Apps Script projects.

Source: Keep a Journal of Special Moments using Twilio Programmable SMS and Google Sheets

Enhanced menus in Google Sheets improves findability of key features (and an important update for Google Workspace Developers)

New Google Sheets menus
New Google Sheets menus

We’re updating the menus in Google Sheets to make it easier to locate the most commonly-used features. Some of your favorite menu items may have moved a little, but all existing functionality is still available. We hope that their new home will be more intuitive and make it easier and faster to navigate the product.

Google Workspace Updates

Google have updated the menus in Google Sheets to help with discoverability. For Google Workspace Developers one of the big changes is the new Extensions menu which replaces ‘Add-ons’ and includes links to Macros, Script Editor and AppSheet. Whilst painful if you need to update support documentation the new structure will hopefully make the location of these tools more intuitive. We covered the Google Sheets menu update in the latest episode of Totally Unscripted:

Source: Enhanced menus in Google Sheets improves findability of key features

Bulk create Google Drive Folders and add Files 2.0

Bulk create Google Drive folders with optional files copied into each one. With full control over naming.

Bulk create Google Drive folders with optional files, from a Google Sheet

Bulk create Google Drive folders with optional files, from a Google Sheet

This post is a variation of this Bulk create Google Drive Folders and add Files tool post.

Key Functionality

  1. Maximum runtime – in order to prevent the tool from reaching the limits imposed by Google you can adjust the number of minutes the tool can run for. Change this in the ‘GlobalVariables.gs’ file in the Script Editor.
  2. Continue from where it left off – if you have a lot of Google folders and files to handle and the above runtime is reached the tool will complete the current row/folder and prompt you to run it again, without duplicating any folders or files already created.
  3. Popups – as well as the ‘Log’ sheet the tool displays a direct popup to the user if it encounters a problem.
  4. Full naming control – you have the ability to tweak every single folder and file name that is created by the tool. See the Concatenate section below to understand the true power of this.
  5. Clickable links – created by the tool which means you can navigate directly to the new folders and files directly from the Google Sheet.
  6. Add more files – the tool currently has columns for 10 files to be added to each folder, but you can actually append more as long as you follow the same convention of 2 columns per file (1 file name and 1 file ID). Do this before you run the tool as you cannot add more files to a folder once it has been created.
  7. Add more folders – after running the tool you can either choose to clear all of the data within it (use the ‘Reset sheets’ option from the ‘Admin’ menu) and start again or you can simply append further rows/folders and re-run without any duplication (if the ‘Folder Link’ column is not blank/empty then it will skip that row).

Source: The Gift of Script: Bulk create Google Drive Folders and add Files 2.0

Append List Items, Paragraphs and table cell items with a date-time stamp in Google Docs using Google Apps Script – Yagisanatode

Learn how to append a date-time stamp to list items, paragraphs and more in Google Docs with Google Apps Script

A very slick example from Scott ‘Yagi’ Donald which shows a very simple method for appending text at the current cursor position and all achieved in just over 50 lines of code, with comments. Head over to the source for the full code and explanation.

Source: Append List Items, Paragraphs and table cell items with a date-time stamp in Google Docs using Google Apps Script – Yagisanatode

Mergo: Designing a template gallery in a Google Workspace add-on

Mergo is a mail merge add-on for Gmail. With the new gallery, users can easily create, share & reuse templates.

Instead of starting your email from scratch, you can open the gallery and select a template: it will appear as a draft in Gmail.

We recently had Romain Vialard join us on Totally Unscripted to talk about some of his add-ons. This post is another example of Romain’s continual exploration of Apps Script, this time looking the new Grid view for the CardService. You an find out more in the source link.

Source: Mergo: Designing a template gallery in a Google Workspace add-on

Creating a web app with Google Sheets

A wonderful set of video tutorials showing you how to set up a web app and connecting it to a Google Sheet. It starts off with a simple form and covers areas like getting data from a sheet and sending it back and using Materialize CSS for to easily create a good looking app. Plus, it also looks at connecting a Google Calendar to the app and how to create multiple pages in the web app.

Sending Outlook Emails using Microsoft Account with Google Apps Script

This is a sample script for sending Outlook emails using Microsoft account with Google Apps Script. Before you use this script, please install OnedriveApp which is Google Apps Script library. And, please authorize your Microsoft account for using Microsoft Graph API.

The author of this contribution, Kanshi Tanaike, always comes up with interesting Google Apps Script solutions. This particular example uses a OnedriveApp library which makes it easy to interact with other part of the Microsoft Graph API expanding the possibilities.

Source: Sending Outlook Emails using Microsoft Account with Google Apps Script

Issues reporting with translation [using Google Apps Script and Google Forms/Sheets

In this post Baz Roberts highlights the benefits of Google Apps Script Language Service to translate Google Form responses into another language.

Source: Issues reporting with translation

Totally Unscripted: Enough of this sheet. A look at Coda with Eric Koleda, Developer Advocate @coda_hq

As part of our last episode of Totally Unscripted we spoke to former Google Workspace DevRel Eric Koleda about his new role supporting the Coda community.

Coda provides word-processing, spreadsheet, and database functions. It’s a canvas that blends spreadsheets, presentations, apps, and documents together. The software can integrate with third-party services like Slack and Gmail.

Eric gave a fantastic overview of the Coda platform highlighting opportunities for developers to integrate with services like Google Apps Script via the Coda REST API and the new Coda Packs, which are currently in beta.

The clip is an excerpt from the full show where we get to see these features in action and some additional resources referenced are linked below:

Sending Multiple Emails using Batch Request with Gmail API using Google Apps Script

This is a sample script for sending multiple emails using the batch request with Gmail API using Google Apps Script. When multiple emails are sent using “ GmailApp.sendEmail ” and “ MailApp.sendEmail ”, a loop is used. But in this case, the process cost becomes high. In this post, I would like to introduce the sample script for reducing the process cost under this situation. Gmail API can be requested with the batch request. The batch request can be processed with the asynchronous process. By this, I thought that the process cost for sending multiple emails. So, this sample script sends multiple emails using the batch request with Gmail API.

I imagine many in the Apps Script developer community have a script or two that sends email. This post highlights a solution using the Gmail API to send emails in a batch, keeping in mind the limitations highlighted in the post.

Source: Sending Multiple Emails using Batch Request with Gmail API using Google Apps Script

Create a book tracker using Apps Script and the Google Books API in 10 minutes

I wanted a quick and easy way for my kids to track the books they read, as well as a way for my wife to catalog all the books in the house. I know there are other services out there like Goodreads that can store your book list…but I just wanted to house the data myself, and not have to create accounts for my young kids.

Nice little project that glues together Google Forms and Sheets and the Google Book API with a little but of Google Apps Script. Follow the link for more information and the source code.

Source: Create a book tracker using Apps Script and the Google Books API in 10 minutes

Announcing the Google Forms API

Google is proud to announce the Google Forms API! The Forms API is currently available in Restricted Beta, with Open Beta expected to follow in Q4. … The new Google Forms API provides programmatic access for managing forms and acting on responses, empowering developers to build powerful integrations on top of Forms.

The Forms API is currently in Restricted Beta. We encourage you to apply here to be an early adopter to get started with the API today! We’ll also send you important updates about Open Beta and improvements to the API. To keep up to date with all the APIs of your favorite Google Apps, please subscribe to the Google Workspace Developer Newsletter.

On Totally Unscripted we were delighted to have Christian Schalk (Developer Advocate, Google) and Hannah Pho (Software Engineer, Google) as well as early Forms API partners Charles Kemp (Strategic Alliances Manager, Zapier) and Charles Wiles (CEO, Zzish), talk about the new Forms API. A recording of the show is on YouTube and show notes will soon be added to the Totally Unscripted website.

Source: Announcing the Google Forms API

Bulk convert Google Docs to PDFs 2.0

Bulk convert all the Google Docs within a given folder to PDFs and optionally delete the Docs afterward, version 2.0

The following Google Apps Script tool is designed to bulk convert all Google Docs within a given Google Drive folder into PDFs. You can choose the destination folder for the PDFs to be put into and also whether you want the original Docs to be deleted. This tool does work on Shared drives.

It is an improved version of this Google Doc to PDF tool post.

Improvements / Features

  1. Maximum runtime – in order to prevent the tool from reaching the limits imposed by Google you can adjust the number of minutes the tool can run for. Change this in the ‘GlobalVariables.gs’ file in the Script Editor.
  2. Continue from where it left off – if you have a lot of Google Docs to convert and the above runtime is reached the tool will save its progress and prompt you to run it again, avoiding any file duplication.
  3. HTML popup – as well as the ‘Log’ sheet the tool now displays a direct popup to the user if it encounters a problem.
  4. PDF counter – after successfully running the tool will include the number of PDFs created as part of the success popup to the user.

Source: The Gift of Script: Bulk convert Google Docs to PDFs 2.0

Create Google Classrooms using Google Apps Script

Creating a Google Classroom and maintaining it can be a bit hectic for so many students in a school. Say you are the moderator of your school and your task is to create 10 Google Classrooms for different teachers. Manually doing this can be a very hectic process, that’s where Google Apps Script comes in.

Using Google Apps Script, you can automatically create Google Classroom with the data in the Google Sheet, and paste the Class code in the Google Sheet.

Source: Create Google Classrooms using Google Apps Script

Executing Function with Minutes timer in Specific Times using Google Apps Script [and tips on creating triggers in V8]

This is a sample script for executing a function with the minutes timer in the specific times using Google Apps Script. For example, when this sample script is used, the following situation can be achieved: Execute a function every 10 minutes only in 09:00 – 12:00 and 15:00 – 18:00 for the weekday.

A clever bit of coding from Kanshi Tanaike for running a Google Apps Script on a defined schedule. The part I found particularly interesting was the solution for programmatically setting triggers in the V8 runtime:

This sample script used Web Apps. Because when v8 runtime is used, when the trigger is set from the function executing by a trigger, the trigger is disabled. This is the recognized bug. But unfortunately, this has still not been resolved. (September 21, 2021) But, when the trigger is installed by Web Apps, no issue occurs. So, please deploy Web Apps.

For the sample code and explanation visit the source link.

Source: Executing Function with Minutes timer in Specific Times using Google Apps Script

How to Make Your NPM Package Available in Google App Script

Boboss74, CC BY-SA 4.0, via Wikimedia Commons

I recently came across GAS — Google App Script and immediately the first hurdle I had to overcome to was make my NPM packages available on there. I had to hop through a couple loops to make this happen. I wanted to call out the steps in this blog so it’ll hopefully help someone out there as well.

In Pulse we’ve previously highlighted a couple of approaches for using NPM libraries in Google Apps Script. The linked source post come from, recent guest on Totally Unscripted, Nima Poulad, Senior Software Engineer at DocuSign. Nima highlights a quick and dirty approach of using eval() on hosted NPM libraries, but goes on to show how Browseify can be used to convert NPM libraries, highlighting some considerations for this approach.

Source: How to Make Your NPM Package Available in Google App Script

Subscribe to Apps Script Pulse...