Copy macros to other Google Sheets (and how to centrally create/update Apps Script code to Google Workspace editors)


Image credit: Google

Manually copying Google Sheets macros from one spreadsheet to another can be time consuming and error-prone. This Google Workspace Add-on automatically copies a script project and attaches it to a user-specified spreadsheet. Though this solution focuses on Sheets macros, you can use it to copy and share any container-bound script.

Are you looking for a way to centralize your codebase but deploy it to container-bound scripts? Here is a tutorial for you from the Apps Script samples on the Google Developers site, highlighted by Steve Webster, which gives an alternative approach to deploying as an add-on or sharing a container-bound template.

In the tutorial, you’ll find code that uses the Apps Script API to perform functions like getting, creating, and updating container-bound scripts. As part of the solution you’ll see how you can get the source project content using the Apps Script API, then opening a target Google Sheet to either create or update the container script.

This solution is not limited to Google Sheets only. You can use the same process to update code in other Google Workspace editors like Docs, Forms, and Slides. Usually, I would recommend maintaining and distributing your code as an add-on, however, in certain scenarios, this alternative approach could work well.

Source: Copy macros to other spreadsheets  |  Apps Script  |  Google for Developers

Google Chat developer platform release notes – new Chat APIs generally available

Video credit: Pablo Felip Monferrer / @pfelipm

Build Chat apps that bring your services and resources right into Google Chat, letting users get information and take action without leaving the conversation.

Google has been spending a lot of effort recently adding features to Google Chat. This just hasn’t been limited to the user interface and Google recently announced a long list of new Chat API methods are now generally available. For Google Workspace developers and administrators this opens up more opportunities to manage chat messages, spaces and membership.

For Apps Script developers I think it’s unlikely you’ll see a ChatApp or a Chat Advanced Service, but with an OAuth2 library you’ll be able to make calls to the Chat REST API. With the new Google Chat API methods comes the ability to access user data and perform operations on the authenticated user’s behalf. I don’t think it will take long before we see example solutions appearing from the community and given this video from Pablo Felip Monferrer I’ll be keep a close eye on his blog

Source: Google Chat developer platform release notes  |  Google for Developers

Generate and send PDFs from Google Sheets with Google Apps Script 

Automatically create PDFs with information from sheets in a Google Sheets spreadsheet. Once the PDFs are generated, you can email them out directly from Sheets. This solution focuses on creating custom invoices, but you can update the template and script to fit your needs.

Today as part of a customer Google Apps Script introduction workshop, we covered a basic invoice / PDF example. By coincidence this month’s Google Workspace Developers Newsletter highlighted a solution spotlight from the Apps Script samples catalogue which does something very similar.

A trick I learned today from our Lead Workspace Trainer, Tim McLardy, was rather than using UrlFetchApp to get a PDF version of the Google Sheet with the export/?format=pdf url, is instead getting the Google Sheet as a blob and creating a pdf with DriveApp.createFile(). As this technique will generate a PDF with pages with all your Sheet tabs the trick is to selectively hide all but the tabs you want in your PDF. In the case of the example linked as the source post you can rewrite the createPDF() function as:

 * Creates a PDF for the customer given sheet.
 * @param {string} ssId - Id of the Google Spreadsheet
 * @param {object} sheet - Sheet to be converted as PDF
 * @param {string} pdfName - File name of the PDF being created
 * @return {file object} PDF file as a blob
function createPDF(ssId, sheet, pdfName) {
  // Based on
  const ss = sheet.getParent();
  const sheets = ss.getSheets();
  // Hides all sheets that are not the main one
  sheets.forEach(s => {
    if (s.getSheetName() !== INVOICE_TEMPLATE_SHEET_NAME) {   
  // make a pdf of the sheet
  const folder = getFolderByName_(OUTPUT_FOLDER_NAME);
  const pdfFile = folder.createFile(ss.getBlob()).setName(pdfName);
  // show all the sheets
  sheets.forEach(s => s.showSheet())
  return pdfFile;

Source: Generate & send PDFs from Google Sheets  |  Apps Script  |  Google Developers

Apps Script has deprecated the Contacts service. Instead, use the People API advanced service | Release Notes | December 16, 2022

Apps Script has deprecated the Contacts service. Instead, use the People API advanced service. Refer to Migrate from Contacts service to People API advanced service.

Having taken bit of a break from Pulse over the festive period I had missed the December release note announcing the deprecation of the Contacts service, ContactsApp. There isn’t much time to update your code as the service will no longer function after April 30, 2023. Google have however provided detailed guidance on migrating from Contacts service to People API advanced service.

[HT to Pablo Felip @pfelipm for flagging this update on Twitter]

Source: Release Notes  |  Apps Script  |  Google Developers

Apps Script added a new method to the Utilities class parseDate(date, timeZone, format) | Release Notes  | November 3, 2022

Apps Script added a new method to the Utilities class. parseDate(date, timeZone, format) parses a provided string date according to the specification described in the Java Standard Edition SimpleDateFormat class.

Given the popularity of handling data using Google Apps Script I’m sure many will welcome the inclusion of a parseDate() method which was included in the most recent release notes.

Source: Release Notes  |  Apps Script  |  Google Developers

Updated Apps Script integrated development environment will replace the legacy experience by Q4 2022 | Release Notes  |  September 27, 2022

Apps Script has turned down the legacy integrated development environment (IDE) in favor of the redesigned IDE that launched in December 2020.

Google have announced the updated Apps Script editor will replace the legacy experience by Q4 2022. Whilst the new IDE was a huge step forward there are some core features from the old Script Editor, which will hopefully make it into the new IDE before Q4. The ones I hear most often are access previous script versions and search/replace across project script files. For the later in the meantime I recommend looking at the Black Apps Script Chrome extension. Whilst this extension primarily enables a true dark mode it also lets you search across script files.

Source: Release Notes  |  Apps Script  |  Google Developers

New behaviour in V8 runtime removing script order limitation | Release Notes | June 6, 2022

We’ve been a little remiss highlighting the latest Google Apps Script release notes, but in case you missed it there has been an update to the V8 runtime:

You can now call functions in separate files before they’re parsed. Previously, the V8 runtime required a script file to be parsed before any other file could call the functions it defines.

Now, the order of files in the Apps Script editor doesn’t matter. This means that you can call a function in a different file to assign a value to a global variable—the function is always defined before it’s called. This behavior reflects that of the legacy Rhino runtime.

See the source post for more details and an example code snippet.

Source: Release Notes  |  Apps Script  |  Google Developers

New features in the Apps Script integrated development environment (IDE) | Release Notes  |  April 13, 2022

April 13, 2022
You can now perform the following actions in the new Apps Script integrated development environment (IDE):

You can read more about the script editor updates in this related Google Workspace Updates post, which provides more details of note is:

This launch ensures Apps Script users can utilize the legacy features in the new environment:

  • Script Properties: Allows you to store simple data in key-value pairs scoped to one script. Script properties are typically used to store developer configuration data. The new IDE has now integrated the interface to edit, delete and create up to 50 new script properties.
  • Add-on Testing: Allows add-on developers to test their add-ons before publishing them broadly.
  • Time Zone Setting: Allows you to update the time zone of the script and its executions.
  • Rhino Debugging: Adds the ability to debug Rhino subroutines on V8 debugger, without migrating to V8. If the code is not V8 compatible, users will get a warning message.

Source: Release Notes  |  Apps Script  |  Google Developers

For Google Workspace Add-ons, an Attachment class has been added to the Card Service that lets you add custom attachments to Calendar events | Release Notes | March 24, 2022 |  Apps Script  |  Google Developers

March 24, 2022

For Google Workspace Add-ons, an Attachment class has been added to the Card Service that lets you add custom attachments to Calendar events. You can also set an event trigger that fires when the user clicks on the add-on attachment provider in the Calendar dropdown menu. For more information, refer to EventAttachmentTrigger.

You can read more about attaching files to Google Calendar events in this related Google Workspace Updates post:

Google Workspace developers can now create Google Workspace add-ons that attach files to a Google Calendar event from any third-party service. This feature enables developers to create add-ons that support attachments from a wide range of sources beyond Google Drive, such as digital whiteboard, content creation, or file management tools.

The update includes the following additional guidance for developers:

You can now develop add-ons that support attaching files from sources other than Google Drive. To get started, learn more about extending Calendar with Google Workspace add-ons and adding attachments with a callback function.

Source: Release Notes  |  Apps Script  |  Google Developers

The get methods for several color objects in the Spreadsheet Service have been deprecated | Release Notes | March 18, 2022 | Apps Script | Google Developers

March 18, 2022

The get methods for several color objects in the Spreadsheet Service have been deprecated in favor of a new naming convention. The functionality remains the same. For example, the getFontColor() method from the Range class has been replaced with getFontColorObject().

Check out the source link for details of which Spreadsheet Services have been updated.

Source: Release Notes  |  Apps Script  |  Google Developers