How to Share Files in Google Drive with Multiple Users using Google Apps Script – Digital Inspiration

Google Drive Access Screen

A limitation of the Drive API is that you can only share files with one user at a time. Google Apps Script is synchronous – it doesn’t support the async/await pattern of JavaScript Promises and you therefore cannot run the code in parallel.

There’s however a simple workaround to help you share a file or folder in Google Drive with multiple users in one go in parallel using the UrlFetchApp service.

On Pulse Phil recently shared a snippet for changing a Google Drive file permissions from Editor to Viewer. In this example for Amit Agarwal you can see how you can make these types of changes for multiple users without slowing down your script execution time.

Source: How to Share Files in Google Drive with Multiple Users – Digital Inspiration

The ULTIMATE Guide to NPM Modules in Google Apps Script

The What and Why of NPM Modules

Google Apps Script has some amazing built-in stuff. It gives us native access to all Google apps like Sheets and Gmail, seamlessly integrates with GCP services like BigQuery, allows for the building of interfaces with HTML and CardService, facilitates the creation of simple webhooks/APIs and web apps with simple and efficient client-server communication, can make use of any API through UrlFetchApp, and can be bundled into add-ons for efficient distribution. In my experience, it’s enough for 99% of all Google Apps Script developers.

However, one thing that Google Apps Script is missing is modules. NPM has an extremely impressive database of JavaScript modules that don’t automatically integrate with Apps Script. Of course, in Apps Script we have libraries, but the selection is extremely limited and there is no marketplace for those. By the way, who wants to participate in creating one? Let me know in the comments! However, the very first warning on the libraries documentation page notes that libraries make Apps Script slow. Well, Apps Script is already far from being the fastest programming language on Earth, so slowing it down further is not an idea that I’m a fan of!

But what if I told you that there actually is a way to use some NPM modules in Apps Script? You just need to bundle them with Webpack. Not sure what I mean? Keep reading.

Source: The ULTIMATE Guide to NPM Modules in Google Apps Script.

Are the benefits of automation worth the overhead of setting things…

Image credit: Brightec

Why setting up automation is always worth it

As developers, we often find ourselves weighing up the benefits of automation against the overhead of setting things up. This is true for our personal workflows as well – I often have an idea of how I could automate various arduous tasks, but in reality, it’s difficult to justify doing so.

I ended up going with my gut on this one, and built a ‘calendar sync tool’ that automates the process. It turned out to be 100% worth it – and in this article, I’m going to argue that it’s always worth automating!

This post from Jonny Townend at Brightec is a two-for-one both highlighting the benefits of taking the time to automate workflows and as a bonus sharing a Google Apps Script powered Google Calendar sync tool.

Source: Are the benefits of automation worth the overhead of setting things…

r/GMail – Has anybody here tried to improve Martin Hawksey’s free Mail Merge system? [adding custom attachments to mailing] 

Has anybody here tried to improve Martin Hawksey's free Mail Merge system?
by inGMail

I’m not really a reddit user but this question about adding custom attachments my Google Workspace Solutions Gallery – Mail Merge example landed in my inbox. The solution is, in Google Apps Script tradition, one line of code! The line is:

if(row['Attachment'] != '') emailTemplate.attachments.push(DriveApp.getFileById(row['Attachment']).getBlob());

The logic is:

  1. check if the row value in the column named Attachment 
  2. if true call Drive to open file with attachment id and push to the emailTemplate object.

The line is added just after:

const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);

If you prefer you can also copy this sheet for a working example. To attach different files the Attachment row cell needs to include a Google Drive file id. There are a number of ways you can get Drive file id’s. For example, here is a Getting all files’ file-id from a folder in Google Drive snippet.

If you are interested in more of what is happening in this solution, the emailTemplate object is returned by getGmailTemplateFromDrafts_(). This function gets the Gmail draft message and as well as any attachments including inline images. If you are interested in handling attachments and inline images you can read more in Everything you ever wanted to know about Gmail draft inline images and Google Apps Script but were afraid to ask.

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

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

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 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