In February 2024, Google made a change in Google Sheets to enhance data security. Now, users must provide explicit consent before Sheets can connect to external data sources. This change aims to prevent unintentional data leaks and improve overall data loss prevention within Google Sheets.
Impact on Users:
When manually adding or editing certain IMPORT functions (IMPORTHTML, IMPORTXML, IMPORTFEED, IMPORTDATA) and the IMAGE function in a Google Sheet, a warning banner will appear blocking sending a receiving data until the user gives consent. To enable this functionality, users need to click “Allow access.”
Impact on Developers:
As highlighted in the first source post by Justin Poehnelt, this change caused issues for developers who programmatically create Google Sheets and automate tasks, such as generating PDF documents as the IMPORT and IMAGE functions were blocked.
Solutions for Developers:
Fortunately, Google introduced an update to the Google Sheets API to address this challenge. Developers can now enable external data access programmatically by setting the importFunctionsExternalUrlAccessAllowed property in the Google Sheet metadata. This allows specific IMPORT and IMAGE functions to work without manual user consent.
For the IMPORTRANGE function, a similar API property is currently unavailable. However, an unofficial workaround exists that involves calling an undocumented endpoint using Google Apps Script as detailed in the second source post by Kanshi Tanaike.
Future Expectations:
As Google continues to prioritize data security, there will likely be further enhancements to data loss prevention controls within Google Workspace. Hopefully, future updates will also include official API controls for IMPORTRANGE and other functions to facilitate legitimate data access for developers while maintaining data security.
Following up from last week, we get into how I built the table() method in our ConsolAS class, using test-driven development (TDD). This is a cool way to make sure everything works perfectly by testing each part before we fully build it. We’re aiming to make it work just like the console.table() function in Google Chrome, which is pretty handy.
To subscribe to events using Apps Script, you can now use the Advanced Google Workspace Events service.
In the May 02, 2024 Google Apps Script release notes it was announced that a new Advanced Google Workspace Events service is available to let developers use the Google Workspace Events API directly in Google Apps Script. The Google Workspace Events API is a relatively new API, which was released to developer preview in January, 2024. The Google Workspace Events API uses a subscription model to handle resource changes. When you create a subscription, you specify the target resource (such as a Google Chat space or Google Meet meeting space) and the event types that you want to subscribe to. The API then sends notifications to your specified endpoint whenever an event occurs for that resource and event type.
In the case of the new Advanced Google Workspace Events service the linked documentation includes samples for using the new service with Google Chat. It’s worth noting that the Advanced Google Workspace Events service is designed specifically on managing subscriptions to events. While it lets you to control the flow of information and manage event subscriptions in your Apps Script projects, the actual processing and handling of event data occurs separately.
The linked documentation page provides sample code as well as outlines all the prerequisites.
In this TU Special, we dive into the exciting new features coming to Google AppSheet and take a first look at the new Google Forms integration. For this special our co-host, Martin Hawksey is joined by Steve Franks (Head of Workspace Engineering, CTS Appsbroker). The discussion/demo was originally presented as an internal tech talk conducted at CTS Appsbroker – warning the episode contains some ‘passionate’ language.
Initial key takeaways which we discuss are:
Seamless Data Collection: Google Forms now acts as a first-class data source for AppSheet, eliminating the need for workarounds and additional add-ons. Data collected through forms automatically flows into AppSheet, creating a smooth and efficient workflow.
External Participation: The integration allows external parties to interact with your apps by simply filling out a Google Form. This opens up new possibilities for data collection and process involvement.
Powerful Automations: AppSheet’s automation capabilities can be triggered by form submissions, enabling you to create sophisticated workflows. The example discussed involves sending an email with an embedded AppSheet view upon form completion, allowing for approvals or further actions within the email itself.
Simplified Development: The integration makes building apps even easier, especially for those already familiar with Google Forms. The need for complex configurations or AppScripts is minimized, empowering users to focus on the business logic and desired outcomes.
Versatility: AppSheet has increased functionality, opening up a wider range of possibilities for app development and automation for Google Workspace customers. It can be used to create full-fledged apps with user interfaces or simply act as a behind-the-scenes automation engine.
Overall, the integration of Google Forms and AppSheet should significantly help empower users to build powerful solutions without coding expertise. Read more about “Build AppSheet automations using Google Forms” on the Google Workspace Updates blog.
Here’s the scoop: While tinkering with a project, I needed a way to pinpoint the last row of data in various sheets — essentially to catch any “orphan” values lurking there. Typically, something like console.table() would be the hero of the day, allowing me to effortlessly display data like so:
AI is changing everything and AppSheet is no exception! In this episode, we will welcome Engineering Lead Mike Procopio from the Google AppSheet team to hear how AI is driving innovation, enabling assisted app development, automated insights, smart workflows, and more! So whether you are no-code, low-code or “what’s code?”, join us to find out what the future holds for AppSheet for building powerful, intelligent apps with Google Workspace
In the next episode of Totally Unscripted*, we will welcome Engineering Lead Mike Procopio from the Google AppSheet team. Mike has in-depth knowledge of Google Workspace, you can thank Mike for simpler file upload in Google Drive launched in 2011.
As part of the session we will be asking Mike about implementing lightweight governance strategies using AppSheet policies, ensuring security and control while enabling creativity and fostering a thriving citizen development culture.
We’ll also take a look at some of the new features that are coming soon to AppSheet, including a new admin console which will be available to Core and Enterprise customers for monitoring app usage, manage user roles, and refine governance policies, as well as AppSheet’s new Google Forms integration which will soon be in public preview.
Finally, and not least, we will explore how you can harness the power of AI in AppSheet, looking at the latest AI features, including AI-assisted app creation and the “extract” capability with Gemini, to streamline development and enhance functionality.
You can join the live stream on YouTube and ask your own questions for Mike during the session. If you miss the live event, you can always watch the recording on the same link:https://www.youtube.com/watch?v=N3Rl7DTekMM
Join this community of over 53,600 data analysts and Google Sheets aficionados and get an actionable Google Sheets tip every Monday
With a weekly subscription of over 50K we probably don’t need to flag a ‘Monday morning espresso’ of Google Sheets tips from Ben Collins as you are probably already subscribed. However, given Ben recently published the 300th issue it’s a milestone worth celebrating and a chance to say ‘thank you’ to Ben for this invaluable publication.
As eloquently demonstrated on a recent episode of Totally Unscripted, Ben is the Google Sheets “King of Functions”. Ben’s expertise isn’t limited to solving Google Sheet challenges, he’s also a master at explaining his thinking and helping other people learn. If you haven’t subscribed to Ben’s newsletter follow the source link (it’s free!) and check out Ben’s website for loads of great tips and resources.
Template for a workflow to track tasks that follow steps. It can alert on due and expired steps per each task, while also navigating to alerted cells with a single click. It also demonstrates the use of conditional rules and ARRAYFORMULA.
Sig Mandel has created this clever Google Apps Script solution which might be suitable for anyone struggling to manage tasks and deadlines in Google Sheets. This template lets you easily configure overdue and upcoming deadlines, letting you jump directly to problem areas with a click. It also cleverly uses conditional formatting and ARRAYFORMULA to streamline your task tracking.
You can use the provided Google Sheets template as a starting point and add your workflow steps as columns, input your tasks, and the sheet takes care of the rest. You can also use a number of other features including real-time “toast” messages, a dynamic installation menu for easy setup, and optimized code for smooth performance.
Learn about the different options for making API calls in Google Apps Script web apps: `google.script.run` and GET/POST endpoints (`doGet` and `doPost`). Understand their strengths and weaknesses to choose the best approach for your project.
Google Apps Script web apps are a popular way to publish user interfaces for automation processes. I’d never really considered the decision between google.script.run and GET/POST endpoints (doGet and doPost) when making server side calls, but as outlined in this post from Justin Poehnelt there are scenarios where both of these approaches might be most appropriate. Justin also provides some useful code snippets to help you get started with both methods. Follow the source link to read more…
All the Google Apps Script related github activity is now on Google Bigquery and Google Looker. Here’s how it was done.
Bruce Mcpherson has recently revisited his work on the visual Google Apps Script explorer tool, scrviz. Bruce has been working diligently to address limitations with the Github API and overcome quota restrictions. This new iteration still allows users to explore Google Apps Script projects and creators, but this time through a shared Looker Studio dashboard. The underlying dataset has also been made available on BigQuery if you would like to do your own analysis.
Follow the source link to find out more and explore the data!
