Chanel Greco is back with more exciting news for Google Workspace developers in the latest Google Workspace Developer News video. As always Chanel is able to pack in lots of detail in under 4 minutes, and if you prefer text over video here are the highlights!
Spreadsheet Class Gets a Boost:
The Apps Script Spreadsheet class has received a handy new method: getSheetById(). This allows developers to directly fetch a sheet within a spreadsheet using its unique ID, simplifying sheet management within scripts.
Transparency for Calendar Events:
Developers can now control the transparency of Google Calendar events programmatically. The new functionality allows setting an event as TRANSPARENT (showing the calendar as Available during that time) or OPAQUE (showing the calendar as Busy). This granular control enhances calendar integration and automation possibilities.
Deprecation Notice:
While new features are welcomed, it’s also important to note deprecations. The getUrl() method for CellImage, CellImageBuilder, and OverGridImage classes within the Apps Script Spreadsheet service is now deprecated. This change reflects the fact that an image’s source URL isn’t consistently accessible regardless of the image insertion method.
Workspace Policy API Goes GA:
A significant announcement for Workspace administrators is the general availability of the Workspace Policy API. This API enables programmatic access to over 60 Google Workspace Admin settings, making auditing and monitoring the Workspace environment more efficient.
Open-Source Drive Picker on NPM:
The Google Drive Picker web component has been published to NPM, making it easier for developers to integrate the Google Picker API into their web applications. This open-source component, designed as a reusable custom HTML element, simplifies the process of adding Google Drive file selection capabilities to web projects.
Chat App Enhancements:
For developers working with Google Chat, there are several exciting updates, primarily available through the Developer Preview Program. These include:
Quick Commands: Users can interact with Chat apps more quickly and efficiently using quick commands, which invoke the app directly without requiring slash commands or additional input.
AppCommandPayload for Slash Commands: Chat apps built as Google Workspace Add-ons now utilize AppCommandPayload instead of MessagePayload for handling slash and quick commands.
Carousel Support: Chat apps can incorporate carousels to display scrollable collections of items, further enhancing the user experience.
Announcement Space Management: Developers can programmatically create announcement spaces and manage space permission settings using the Chat API with App Authentication.
Expanded UI Widget Library: More Google Workspace Add-on UI widgets are now available for Chat apps, providing developers with a richer set of tools for creating interactive experiences.
Stay Up-to-Date:
To learn more and explore these features in detail, visit the Google Workspace Developers YouTube channel and check the video description for links to relevant documentation.
4 Approaches to extracting the body text of a Google Doc with Google Apps Script. Full Code and tutorial
Scott Donald must be one of the most comprehensive Google Apps Script writers I know. All of his tutorials are packed with information and useful tips. This recent post is no exception, as Scott dives in and shares this detailed guide on retrieving a Google Docs body text using Google Apps Script.
The tutorial explores four approaches to extracting text from a Google Doc:
DocumentApp: This approach is straightforward for basic text extraction but may not capture all elements, especially “Smart Chips.”
DocumentApp with Element Iteration: This method allows for extracting text and URLs from standard text and supports some “Smart Chips” like Date, Rich Link, and Person.
OCR Approach: This involves converting the document to a PDF, applying OCR, and reading the extracted text. It captures most displayed text but may not recognise emojis or some formula symbols.
Docs API Advanced Service: This approach utilises the Docs REST API to access text, links, and specific “Smart Chip” data. It offers more control over data extraction but may require navigating complex JSON responses.
Scott’s tutorial provides a comprehensive and practical guide to retrieving Google Docs body text using Google Apps Script. Be sure to check out the full tutorial for detailed explanations, code examples, and helpful tips. And don’t forget to share your preferred approach and any challenges you’ve encountered on Scott’s post.
Starting today, the OAuth consent screen will now let users specify which individual OAuth scopes they would like to authorize. For example, if a script requests access to a user’s Sheets and Forms files, and the users only intends to use the script with Sheets files, they can decide to only allow access to their spreadsheets and not their forms. This affords users the benefit of more granular control over what data their 3P applications are allowed to access.
Exciting news for Apps Script developers and users! Google has taken the first step towards more granular control over OAuth consent in Apps Script. This means users can now choose exactly which services their scripts can access, leading to a more secure and transparent experience.
What’s New?
Previously, when running a script that required access to multiple services (like Google Sheets and Forms), users had to grant permission to all or nothing. This all-or-nothing approach could make some users hesitant to use scripts, especially if they were only comfortable granting access to a subset of the requested services.
With the latest update, the OAuth consent screen now allows users to authorize individual services. For instance, if a script needs access to both Google Sheets and Forms, but the user only intends to use it with Sheets, they can simply grant access to Sheets and deny access to Forms.
This change is currently being gradually rolled out to the Apps Script IDE, with expected completion by January 24, 2025.
Enhanced Security and Transparency
This granular control over OAuth consent is a significant step towards improving the security and transparency of Apps Script. Users now have more control over their data and can make informed decisions about which services they want to allow their scripts to access.
This change also benefits developers, as it can help build trust with users and encourage wider adoption of their scripts.
A Glimpse into the Future: Chat Apps and Workspace Add-ons
While currently limited to the Apps Script IDE, this granular OAuth consent model will eventually be implemented in other Apps Script surfaces, notably Chat apps and Workspace Add-ons. This has implications for developers working in these areas.
For Chat app and Workspace Add-on developers, this means it’s time to start thinking about how this granular consent model will impact your development process and user experience. You can start experimenting with the new methods added to the ScriptApp and AuthorizationInfo classes to programmatically handle different consent scenarios.
Digging Deeper: AuthorizationInfo
To help developers manage these granular permissions, Google has introduced the AuthorizationInfo class. This class provides methods to check authorization status, retrieve authorized scopes, and even generate authorization URLs. Here’s a quick look at some of its key functionalities:
getAuthorizationStatus() : This method allows you to determine if a user needs to authorize the script to use one or more services.
getAuthorizationUrl() : This method returns an authorization URL that can be used to grant access to the script.
getAuthorizedScopes() : This method returns a list of authorized scopes for the script.
These methods give developers fine-grained control over the authorization process, enabling them to build more robust and user-friendly applications. Google have already published a great developer guide on how to Handle granular OAuth permissions.
Learn More
To learn more about this update, be sure to check out the official announcement provided in the source link.
Discover a treasure trove of scripts at The Awesome Script Site. Developed primarily for Google Workspace, these scripts offer solutions to common challenges faced by businesses and organizations. Created by Jonas Lund and soon other contributors, these scripts are free to use and come with comprehensive documentation. Share this resource with your network and remember to visit often for new additions.
The Awesome Script Site, by Jonas Lund, is a “treasure trove of scripts” for both users and Google Workspace administrators. The administrator scripts simplify various administrative tasks, but you will require a Google Workspace admin role to use them. Due to their handling of sensitive user data obviously please take time to review the scripts to make sure you are happy with how they work.
The scripts cover a wide range of functions, including:
Alias Management: Manage user email aliases directly from a Google Sheet.
Chromebook Reporting: Fetch and organize Chromebook data from your Google Workspace.
Group Member Management and Reporting: Bulk add or remove group members and generate group member reports.
OU Management: Create, list, and delete Organizational Units (OUs) based on specific criteria.
It’s worth spending a little time exploring the Awesome Scripts site to discover these admin scripts and more. To keep up-to-date on new ‘Awesome Scripts’ you can follow Jonas Lund on LinkedIn. Follow the source link to get the scripts and read more.
When working with Google Sheets, you often use formulas like IMPORTRANGE to transfer data between files. However, IMPORTRANGE is not always reliable, especially when files become too large and you see the message “The file is too large.” In such cases, switching to a Google Apps Script-based solution that uses CSV files for data export and import is more efficient and flexible.
Here’s how you can create a system to export data from a Google Sheet to a CSV file and then import it into another sheet.
Export Data Script
This script exports data from a sheet named YOUR_TAB_NAME to a CSV file saved in Google Drive.
function exportToCSV() {
const SheetName = "YOUR_TAB_NAME";
const NameFile = "YOUR_FILE_NAME";
const ss = SpreadsheetApp.getActive();
const sourceSheet = ss.getSheetByName(SheetName);
// Get the values as displayed in the sheet
const dataValues = sourceSheet.getDataRange().getDisplayValues();
// Creating CSV content
const csvContent = dataValues.map(row => row.join(";")).join("\n");
// Check if there is data
if(csvContent == "") return;
try {
const fileId = DriveApp.getFilesByName(NameFile + ".csv").next().getId();
DriveApp.getFileById(fileId).setContent(csvContent);
} catch {
DriveApp.createFile(NameFile + ".csv", csvContent, MimeType.CSV);
}
}
How It Works
Fetches data from the specified sheet (YOUR_TAB_NAME).
Creates CSV content, joining data with the ; separator.
Updates the CSV file if it already exists or creates a new one.
Import Data Script
This script imports data from a CSV file into a Google Sheet named YOUR_TAB_NAME.
function importFromCSV() {
const SheetName = "YOUR_TAB_NAME";
const NameFile = "YOUR_FILE_NAME";
const Separator = ";"; // Change your separator if needed
const destinationSheet = SpreadsheetApp.getActive().getSheetByName(SheetName);
let fileId;
try {
// Search for the file and get its ID
fileId = DriveApp.getFilesByName(NameFile + ".csv").next().getId();
} catch {
Logger.log("Il file '" + NameFile + ".csv' non è stato trovato in Google Drive.");
return;
}
const file = DriveApp.getFileById(fileId);
const property = PropertiesService.getDocumentProperties();
// Check if the last imported data has already been loaded
const lastModified = property.getProperty("lastModified");
const timeStamp = file.getLastUpdated().toUTCString();
property.setProperty("lastModified", timeStamp);
if (lastModified == timeStamp) return;
// Retrieve the CSV content
const csvContent = file.getBlob().getDataAsString();
// Split the content into rows and then into columns using the specified separator
const data = csvContent
.split("\n") // Split into rows
.map(row => row.split(Separator)); // Split each row into columns using the specified separator
destinationSheet.clearContents();
destinationSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
How It Works
Finds the CSV file in Google Drive.
Checks for changes by comparing the file’s last modification time with a stored property value.
Reads data from the CSV file and imports it into the sheet, clearing existing data first.
Automation with Triggers
To automate the import process, you can use a time-based trigger to run the script at regular intervals (e.g., every minute, hour, etc.).
Setting Up a Trigger
Go to Apps Script Editor (Extensions > Apps Script).
Click on Triggers (Clock icon) or Tools > Triggers in the editor.
Create a new trigger:
Choose the importFromCSV function.
Select “Time-driven trigger.”
Specify the frequency (e.g., every minute).
With this trigger, your script will regularly check for updates in the CSV file and automatically import new data.
Explanation of the Separator Usage
Why the semicolon (;) is used: Descriptions or text fields may already use commas (,), and using them as a separator could lead to incorrect data splits. Using semicolons avoids this issue.
Alternative separator: If semicolons (;) are also present in the data, it’s recommended to use a unique symbol, such as §.
To update the separator, replace “;” with “§” in the following line:
.map(row => row.split("§"));// Update the separator here
This makes the script adaptable to various data scenarios.
Why Use This Method?
Advantages
Avoid IMPORTRANGE Limits: No errors related to overly large files.
Efficiency: Data is transferred as CSV, reducing connectivity issues between files.
Automation: Imports happen without manual intervention.
Limitations
Maintenance: Scripts need to be managed and updated manually if changes occur.
Security: Ensure file and script access is secure.
With these scripts and a configured trigger, you can reliably and efficiently handle large volumes of data between Google Sheets. If you need further customizations or help setting it up, feel free to ask!
It’s time for a special “rewind” of top community contributions to AppsScriptPulse in 2024. As part of this revisit, here are the hottest posts that got everyone buzzing each month of the year.
January: The Gemini API and Function Calling (with Martin Hawksey)
Kicking off the year was my own “GenAI for Google Workspace: Exploring Gemini API Function Calling with Google Apps Script — Part 3“. This post delved into the powerful synergy of Gemini and Apps Script, showcasing how to create personalized mail merges using Google Sheets. The key takeaway? Gemini can define functions that your code can actually execute – opening up a whole universe of creative solutions with Apps Script at the helm!
February: Mastering Smart Chips in Google Sheets (with Ben Collins)
February saw us diving headfirst into “The complete guide to Smart Chips in Google Sheets” by Ben Collins. This post was like a treasure map to unlocking the hidden potential of Smart Chips in Google Sheets. We learned how to wrangle and extract data like pros, especially with that super useful dot syntax in formulas. The focus was on the no-code awesomeness of Smart Chips, but Ben teased us with a promise of more developer-focused insights on the Totally Unscripted show – talk about leaving us wanting more!
March: Conquering the CASA Tier 2 Security Assessment (with Kelig Lefeuvre)
March brought a bit of a challenge – navigating the CASA Tier 2 Security Assessment. But fear not, because “Guide to completing Casa Tier 2 Security Assessment for Google Apps Script (and how to scan your Google Apps Script project for CASA)” by Kelig Lefeuvre came to our rescue. Kelig, a Product Engineer at Scriptit & Folgo, provided a developer’s-eye view of the entire process, complete with insider tips you wouldn’t find in the official documentation. This post was a lifeline for anyone looking to publish their masterpiece on the Workspace Marketplace or working with those restricted scopes.
April: Choosing the Right API Call Method (with Justin Poehnelt)
April had us making crucial decisions – “Google Apps Script: google.script.run vs. doGet/doPost Endpoints” by Justin Poehnelt helped us navigate the tricky choice between google.script.run and GET/POST endpoints (doGet and doPost). Justin laid out the pros and cons of each approach, even throwing in some code snippets to get us started. Talk about a helpful hand in picking the perfect tool for our Apps Script arsenal!
May: A Security “Gotcha” and Solutions for Sheets Imports (with Justin Poehnelt)
May threw us a bit of a curveball with Google Sheets’ security updates. “Allowing access for IMPORTHTML, IMPORTDATA, IMPORTFEED, IMPORTXML, and IMPORTRANGE on behalf of the user in Google Sheets” by Justin Poehnelt explained how the enhanced security measures for external data sources impacted both users and developers. Thankfully, Justin also provided the antidote – we learned to use the Sheets API to grant access programmatically, making sure our automations continued humming along smoothly.
June: Bridging AppSheet and Cloud SQL with Ease (with Vo Tu Duc)
June brought us the “ultimate” guide – “The Ultimate Guide Connecting AppSheet to Google Cloud SQL and MySQL databases” by Vo Tu Duc, a Google AppSheet GDE. This post was like having a personal tutor guiding us through the entire process of setting up Google Cloud SQL, building a MySQL database, and weaving it all together with AppSheet. Vo also explained why this setup is the holy grail for large-scale AppSheet apps that need a bit more muscle than Google Sheets can provide. Screenshots galore made this guide a breeze to follow, even for Cloud newbies!
July: Docs and Markdown: A Match Made in Apps Script Heaven (with Kanshi Tanaike)
July saw the worlds of Google Docs and Markdown colliding – in a good way! “Using Google Drive API and Google Apps Script to convert between Google Docs and Markdown” by Kanshi Tanaike highlighted the awesome new ability to export Docs to Markdown and bring Markdown files into Docs. Kanshi shared sample scripts for both conversions using the Drive API, paving the way for automation wizards to work their magic, especially when dealing with GenAI output in Markdown format.
August: Happy Birthday, Apps Script!
August was a time for celebration – “Celebrating 15 years of Apps Script 🎉 🥂” when we commemorated the journey of our favourite scripting tool. We got a link to a festive YouTube Short and took a reflective look at how far Apps Script has come, where it’s headed, and why it’s more relevant than ever in this age of GenAI.
September: Workspace Developer News – Docs, Meet, Chat, and More (with Chanel Greco)
September brought a whole bag of treats in the Workspace Developer News roundup – “Google Workspace Developer News: Create and organize Docs with the new ‘tabs’ API methods and more” by Chanel Greco. We learned how to use the Docs API or Apps Script to become masters of tabs in Google Docs, plus we got new tricks for Google Meet Add-ons and Chat apps. It was a buffet of updates that kept us busy experimenting all month long!
October: Iterators and Generators – Our Apps Script Power-Up (with Bruce Mcpherson)
October was all about leveling up our Apps Script game – “Optimising Google Apps Script: Efficiently handling large datasets with iterators and generators” by Bruce Mcpherson showed us how to harness the power of these JavaScript concepts to efficiently process large datasets in Apps Script. Bruce, never one to shy away from a challenge, provided clear explanations, practical examples, and even a clever workaround for Apps Script’s lack of native generator support. Talk about a knowledge drop that left us feeling like coding superheroes!
November brought us some serious control-freak vibes (in a good way) with “Going beyond the menu: Programmatic controlling Google Sheets protection using Google Apps Script” by Kanshi Tanaike. We went beyond those basic menu options, learning to use Apps Script and the Sheets API to manage sheet and range protections like true automation ninjas. Kanshi provided use case examples that demonstrated how to achieve granular control and build protection workflows that would make even the most security-conscious spreadsheet guru proud!
December: Exponential Backoff – Taming Those Rate Limit Errors (with Phil Bainbridge)
December had us facing those pesky rate limit errors head-on, armed with the knowledge from “Beginner’s guide to exponential backoff in Google Apps Script for handling rate limit errors” by Phil Bainbridge. This post gave us a beginner-friendly introduction to the art of exponential backoff, complete with sample code to put the theory into action. Phil even shared a real-world scenario where this technique saved the day, proving that even infrequent errors can be tamed with a bit of coding finesse.
That’s a wrap on our AppsScriptPulse “year in review”! These posts were more than just code snippets; they were mini-masterclasses that helped us grow as Google Workspace developers. So, huge thanks to all the authors for sharing their knowledge and expertise! Now, go forth, fellow developers and no-coders, and create amazing things! Happy Scripting!!!
Exponential Backoff, a process by which when something fails you try again a set number of times whilst increasing the delay between each attempt.
Sample Apps Script code for Exponential Backoff
The following Google Apps Script is designed to explore Exponential Backoff – a process by which when something fails you try again a set number of times whilst increasing the delay between each attempt, up to a certain point.
I needed this for a tool I built which adds Guests to a Calendar event from a Google Form submission. Whilst I was using ScriptLock to prevent simultaneous submissions, the code ran so fast that it would infrequently trip the Calendar API with the following error message “API call to calendar.events.patch failed with error: Rate Limit Exceeded”.
By infrequently I mean a reported issue only once in 3,500 submissions over the course of 12 months. Enough however to take the opportunity to learn about Exponential Backoff and to squash that single instance.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
The latest Google Workspace Developer News brings exciting updates, particularly for Google Chat. If you are in the Developer Preview Program there are a number of updates including the ability to build Google Workspace Add-ons that include Google Chat apps, simplifying app creation and reducing the need for separate Chat apps. This will hopefully simplify the installation process for admins and end-users, providing a more seamless experience.
Another helpful update allows the creation of group chats in import mode when migrating to Google Chat from other platforms, facilitating a smoother transition. Additionally, developers can now get or update users’ Google Chat space notification settings and list custom emojis through the Chat API, offering greater control and customisation.
The Calendar API has been enhanced, now providing access to birthday and other special events and enabling the differentiation of regular events from other types using the Apps Script Calendar service.
For detailed information and links to relevant documentation, be sure to check the video description on YouTube.
Bluesky is gaining traction this end of 2024, and if you’re on the platform, you need to know your numbers. Want to track your Bluesky stats without the hassle? Google Apps Script and Google Sheets are here to rescue you with a simple, yet powerful solution.
In this blog post, Stéphane Giron provides a guide to tracking Bluesky social media statistics using Google Apps Script and Google Sheets. With the growing popularity of Bluesky it can be useful and interesting to understand the social media dynamic of the platform.
The script he offers tracks metrics such as the number of posts, followers, likes, reposts, and more. Stéphane also details how to install and set up the script, including copying and pasting the provided code, running initialization functions, and scheduling automatic data collection. The result is raw data that users can then use to create graphs and visualize their Bluesky activity over time.
So, let’s do something different. If you’re facing a technical challenge, I’m opening up a new way to get direct support. 🎯
How It Works: Fill out this Google Form and share the details of your challenge. Here’s what I’ll need from you:
In this blog post, Dmitry Kostyuk offers Google Apps Script users assistance with their coding challenges. Users can submit their problems through a Google Form, providing details about their problem, relevant code snippets, and any additional comments. Dmitry emphasizes that this is a collaborative learning opportunity, not a code-writing service. Submitted challenges might be showcased anonymously on Dmitry’s blog and social media. Follow the source link for more details.
