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.
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 pablofelip.online.
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 https://webapps.stackexchange.com/q/162155/30021
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) {
s.hideSheet()
}
});
// 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;
}
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.
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.
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.
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.
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.
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.
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:
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.