Tuesday 30 November 2021

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");

Tuesday 16 November 2021

Convert Google Doc to PDF in a given folder

As of February 2023 'Convert Drive Files' is now available as a Google Workspace Marketplace Add-on.

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.

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

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

Tuesday 9 November 2021

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:


Tuesday 2 November 2021

Bulk create Google Drive Folders and add Files 2.0

The following Google Apps Script tool was developed to bulk create Google Drive folders with optional files copied in to each one, all from data within a Google Sheet, along with the ability to adjust all folder/file names used.

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

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