How to pass Apps Script data directly into a HTML page

The following Google Apps Script code is designed to create a HTML popup in a Google Sheet and pass some data directly into it so that the popup can display the information.

This was part of a larger project I was working on so I have stripped all of that away here and kept the bare minimum for demo purposes. I already knew how to get the HTML page to run a Function once it had loaded to then access static data from within the Google Sheet, but what was tripping me up here was if that data was coming directly from Apps Script itself - such as an error message from a try/catch.

Display Apps Script code directly in a HTML popup

Change file permissions from Editor to Viewer

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.

Firstly you will need to enable the Drive API in the Script Editor by going to 'Services' > 'Drive API' > 'Add'. Next we will look to get the permission ID for the email address of the user we want to change access for:

var permissionId = Drive.Permissions.getIdForEmail('email address here').id;

Google Add-on: Archive Sheet data

Easily move/archive a row of data from one Google Sheet (tab) to another, within the same file. If you prefer something a bit quicker than cut/paste and with less chance of human-error. It will also remember your individual preferences for each file so it is even quicker to use next time around.

This is a Google Workspace Add-on designed to work in Google Sheets via the Side Panel and is freely available for all. It is a packaged-up version of my previous Archive Sheet data post.

I will be breaking down the new skills I have acquired in future blog posts with the hope of helping you to save time if you do something similar.

Create a new Sheet tab and format a 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.
  

Nicely formatted Header row for appending data to

Google Add-on: Search Drive for owned files

I am happy to announce my first ever official Google Workspace Marketplace Add-on!

Search Drive for owned files - enter an email address to find all of the Google Drive files that are owned by that account and export the results into a Google Sheet for easy viewing.

This is an Editor Add-on designed to work in Google Sheets and is freely available for all. It is a packaged-up version of my previous Search Google Drive for owned files post for those interested in what most of the Apps Script code looks like.

I will be breaking down the new skills I have acquired in future blog posts with the hope of helping you to save time if you do something similar.

Replace text in a Google Doc with a hyperlink

The following Google Apps Script is designed to search the body of a Google Doc for a specific string/pattern (ie a keyword I have used) and insert a clickable hyperlink. Typically I use the JavaScript replace method when going through the body of a Doc and inserting data. However I recently came across a difficulty where I was inserting data into a table that was causing the long hyperlinks to split between 2 lines and hence lose their click functionality. With full credit to this blog post by Yagisanatode I found a way to overcome this.

Starting in the usual manner we pick-up our Google Doc and get its body, so that we can start to work on the content:

var docBody = DocumentApp.openById('Doc ID here').getBody();

Now we look to use the 'findText' method to search the content for our string/pattern. From this we are returned the position (range) and need to 'getElement' with 'asText' so that it can be edited:

var getString = docBody.findText("<<keyword>>").getElement().asText();

Finally we can set the text that will be displayed in the Google Doc in replace of our keyword along with our hyperlink for when a user clicks on it:

getString.setText("Useful link to click").setLinkUrl("www.pbainbridge.co.uk");

Convert Google Doc to PDF in a given folder

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 solutions developed on this blog 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).

The Code

Firstly we get our Google Doc (that we want to convert to a PDF) and the Google Drive folder where we want our PDF to be stored:

  // get Google Drive folder
  var folder = DriveApp.getFolderById('ENTER ID HERE');

  // get Google Doc file
  var file = DriveApp.getFileById('ENTER ID HERE');

Next we get the content of our Google Doc as a PDF (blob) and create our new file in the Google Drive folder:

  // get file content as PDF blob
  var pdfBlob = file.getAs('application/pdf');

  // create new PDF file in Google Drive folder
  folder.createFile(pdfBlob);

Finally, and optionally if you want to comment this part out, we delete the original Google Doc that we no longer need:

  // delete original Google Doc file
  file.setTrashed(true);

Totally Unscripted: The Pulse of the Google Apps Script Community

I was very honoured to be invited to join the Totally Unscripted show on Wednesday 27 October 2021 after achieving one of the top 3 AppsScriptPulse contributions of 2021!

The show is hosted by Martin Hawksey, Charles Maxson and Steve Webster. It was an opportunity to join 2 fellow Apps Scriptors (Romain and Scott Donald) to discuss our backgrounds and experiences. 

Thank you to everybody who has been using this blog as I share my learning with Google Apps Script. Below is an embedded YouTube video of the show: