AppsScriptPulse

The Gift Of Script: Archive Sheet data Add-on

Move/archive a row of data from one Google Sheet (tab) to another, within the same file, using this Google Workspace Add-on. It will also remember your individual preferences for each file so it is even quicker to use next time around.

New Features
January 2022
A new ‘Settings’ page allowing you to select more than just cell values when archiving row data. You can now also select:
> Text formatting: bold, italics, colour, size and style.
> Cell: background colour, Notes and wrapping.

Source: The Gift of Script: Archive Sheet data Add-on

Change file permissions from Editor to Viewer

Google Drive Access Screen

How to change Google Drive file permissions from Editor to Viewer as said user.

The following Google Apps Script is designed to change the Google Drive file permissions of a specific user from Editor to Viewer. The function actually came about when needing to end a process where a user had been editing a Google Sheet on a Shared drive that they should only then have Viewer access to. To complicate matters the Apps Script code is running as said user when it needs to reduce their own permissions.

Source: The Gift of Script: Change file permissions from Editor to Viewer

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.

Google Apps Script community contributions —Summary of 2021 — Aryan Irani

As this amazing year comes to an end, I decided to note down all my blogs, videos in the past year. This has been an amazing year for me academically and professionally. I have passed all my engineering exams and am in my 3rd year of engineering. I am also constantly helping my college automate internal processes using Google Apps Script and Google Workspace. You can check them out in the blog link given below

Looking at his summary post from Aryan Irani we’ve clearly not been able to keep up here on Pulse with the volume of content that they have published. The summary post linked here is an opportunity to review some of Aryan’s top community contributions from this year.

Source: Summary of 2021 — Aryan Irani

Using Google Forms API with Google Apps Script

The Google Forms API is currently in Open Beta. For Google Apps Script users the new Forms API is worth keeping an eye on as it enables functionality not currently included in the native FormsApp service. In this post from Kanshi Tanaike an example is looked at where the Forms API is used to insert an image to a question. If you are interested in trying this example out remember that you currently need to apply for access at the Early Adopter Program page.

Source: Using Google Forms API with 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…

Promote your Google Workspace Marketplace apps with the new badge for developers

Continuing with updates to further improve the developer experience, we are excited to announce Google Workspace Marketplace badges. The new badges will allow developers to promote their published Google Workspace Marketplace applications on their own websites. Users will be taken directly to the Marketplace application listing, where they can review application details, privacy policy, terms of service, and more. These users will then be able to securely install applications directly from the Google Workspace Marketplace.

The post from Google contains more information about the benefits of using a promotional badge and how to get on for your Google Workspace Marketplace add-ons.

Source: Promote your Google Workspace Marketplace apps with the new badge for developers

Create a new Sheet tab and format a header row

Create a new Sheet tab within an existing Google Sheet file and format a header row (font size, colour, alignment, etc) for appending data to.

Header row

Header row

The following Google Apps Script was developed as part of a larger tool for collating file information into a specifically formatted Google Sheet. As the tool was an Add-on I needed to setup the Google Sheet file in advance so that information could be later appended to it. This code:

  • Checks if a particular named Sheet (tab) already exists within the Google Sheet file. If it does then it will delete the existing Sheet and create a brand new one.
  • Reduce the overall number of columns within the Sheet.
  • Insert 1 row of data to use as the Header row.
  • Set the Header row font size, colour, weight (bold) and horizontal/vertical alignment.
  • Set the Header row background colour, height and column widths.

Source: The Gift of Script: Create a new Sheet tab and format a header row

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? from GMail

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.

Google Forms API now available in open beta

The Google Forms API is now rolling out as an Open Beta which means developers who are part of our Early Adopter Program can make their integrations available to the public. We’ll no longer require individual end-user accounts to be allowlisted. … Developers can apply to join our Early Adopter Program and begin developing using the Google Forms API by filling out this form.

In other Google Workspace news the new Google Forms API continues it’s journey to general availability with the announcement of the open beta. Just as the other Google Workspace REST APIs can be useful to Google Apps Script developers it’s useful to keep an eye on what is possible in the Forms API.

Source: Google Forms API now available in open beta

Subscribe to Apps Script Pulse...