In this report, I would like to introduce a sample script for efficiently deleting rows by conditions on Google Spreadsheet using Google Apps Script. Recently, I had a situation for being required to achieve this situation. In my report, it has already known that when Sheets API is used, the rows can be efficiently deleted by a condition. Ref However, in that case, Sheets API couldn’t be used. Under this situation, I came up with a method. In this report, I would like to introduce this method.
Here’s a clever method from Kanshi Tanaike for deleting rows in Google Sheets based on a column condition. The solution makes use of the built-in .removeDuplicates() method, the clever bit is the script first copies the header row into any row that matches the condition. As this creates duplicate rows the .removeDuplicates() method can be called to the entire data range. Using this method Kanshi was able to improve an execute of an earlier function from 67 seconds to 13 seconds!
I recently faced a frustrating issue when writing data to a CloudSQL database with the JDBC class in Apps Script. I kept getting the following error:
Exception: Argument too large: SQL
I also observed that it only happened when my SQL query reached a certain length. I considered breaking it down into multiple queries, but I was still puzzled 🤔 because I was only sending a few dozen kilobytes of data.
Now, the thing is, the official documentation could be more helpful; even though the solution is there, it needs to be better explained. So, I turned to StackOverflow. There was a discussion on this exact topic, but to my surprise, I was still waiting for an answer. Until, well, I wrote it 😉
If you have strong AppSheet technical skills along with solid Google Workspace skills and would like to share your expertise globally as a recognized “Google Developer Expert” (GDE), we would like to invite you to apply to become an official GDE member specializing in AppSheet and Workspace!
The Google Developer Experts program is a great way to get recognised for your abilities, but more importantly an opportunity to get close to the Google product teams to learn and share your day-to-day experiences. Whilst AppSheet is positioned as a no/low code solution it doesn’t mean there aren’t individuals out their developing sophisticated apps and supporting the community along the way.
If this sounds like you the AppSheet team are looking for community experts to become the next AppSheet GDEs. This source post includes some more information as well as a form where you can note your interested. As a GDE of 9+ years I’m also happy to share my experience of the program and what I think it takes to stand out from the crowd.
This guide is not documentation, but a condensed reminder of what you must do.
Max Makhrov has put together this very succinct guide to publishing Google Workspace Editor Add-ons to the Google Workspace Marketplace. As explained by Max the guide isn’t a step-by-step tutorial, but instead provides the key steps you should remember to do. There are some great tips in this post, particularly around what to include in the recorded screencast required by the Google oAuth verification team.
Learn how to seamlessly integrate JSON data from an API into Looker Studio using a custom connector built with Google Apps Script.
This post is a useful reminder of that Google Apps Script can be used to make a data connector for Google online visualisation and reporting tool, Looker Studio. The post by Dimitris Paxinos covers all you need to know about integrating a third-party API as a data source, exposing configuration settings and deploying the connector. All the code is on Github and is a great boilerplate if you have other APIs you are interested in integrating.
Learn how extract all the embedded images from a Google Document or Google Slides presentation and save them as individual files in a specified folder in your Google Drive.
Often I’ll use Google Docs for drafting blog posts. With the introduction of Duet AI having a generative assistant in situ helps with the creative process. As well as text Google Docs is a really simple canvas for quickly copy/pasting screenshots. Copying content from Google Docs to WYSIWYG editors can sometimes be challenging, particularly, when it comes to images.
Next time I encounter this problem I’ll be using the script solution from Amit Agarwal, which can extract images from Google Docs and Slides and save them to Google Drive. I’m sure there are many other situations where this snippet could come in handy.
Check a Google Sheet once per day and if the date is in the past Autofill another row of formulas.
Autofill Google Sheet formulas each day
The following Google Apps Script is designed to check a Google Sheet once per day and if the date is in the past it Autofills another row with the existing formulas used across the columns. This post is a variation of the Autofill Google Sheet Formula one.
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
We are excited to roll out to GA (General Availability) our first visual editing features to all AppSheet creators, starting today.
This a new way for AppSheet creators to make changes to their apps and navigate the editor. It’s intuitive for anyone to point at what they want to change if they can see it. We are now letting you do that in the editor: you can now hover on visual components of the editor’s app view and see possible editor actions for the outlined component.
Google recently announced the rollout of new new visual editing features in AppSheet. The new feature should help simplify and speed up app development (see the source post for an animated gif / video of what it looks like). In the announcement Google mention that the new feature isn’t available for all components:
Outlines are only available for some components visible in the app. For instance, Detail and Form Views have a lot more controls than the Deck View right now. Additionally, only some Editor actions are available, such as navigating users to Data components, View components, Action components and some of the general Settings.
More updates are planned and if you don’t see this feature in AppSheet yet the rollout has been paused for Google Cloud Next and should be available for everyone soon.
Unfortunately, there are no built-in methods for directly managing PDF data using Google Apps Script. Fortunately, after the V8 runtime has been released, several raw Javascript libraries could be used with Google Apps Script. pdf-lib is also one of them. When this is used, PDF data can be cooked over Google Apps Script. In this report, I would like to introduce achieving this using a Google Apps Script library.
Google Apps Script is a powerful tool for automating tasks. It can be used to process data, create spreadsheets, and send emails. One of the limitations of Apps Script is that it does not have built-in support for PDF files. However, there are a number of third-party libraries that can be used to work with PDF files and in Pulse we’ve featured a number of posts from Kanshi Tanaike where they have explored the pdf-lib is a JavaScript library.
This work has culminated in PDFApp, a dedicated Apps Script library created by Kanshi Tanaike based on the pdf-lib, but optimised for Apps Script. The source post includes a number of recipes for handling PDF files listed below:
Are you looking for a way to receive notifications in real-time when an important spreadsheet in your Google Drive get modified or is accidently deleted by sometimes? Well, Google Drive offers an API to help you set up a watch on any file in your Google Drive be it a document, presentation or even a PDF file. This means that you can receive instant notifications whenever the content or even permissions of that file changes.
This tutorial explains how you can setup watch notifications on any file in your Google Drive with the help of Google Apps Script.
As part of the Google Drive API you can set up watch notifications on any file in your Google Drive. This means you can receive instant notifications whenever the content or permissions of that file changes. This feature can be useful for a number of scenarios, for example, if you have sensitive Drive files that you want to closely monitor, or a workflow where you’d like to trigger additional events when a file is updated.
This post from Amit Agarwal explains how you can create a push notification for a Google Drive file using Google Apps Script. As noted in the post you can use Google Apps Script to handle the push notification by creating a doPost Web App, however, not all the response data/headers are available using Google Apps Script. There is a related ticket which had some activity earlier in the year and I would encourage you to star the request to get updates and encourage Google to fix – https://issuetracker.google.com/issues/67764685.
Google Workspace Admins looking for domain/user activity on Drive files might want to look at the Reports API activities endpoint, which can be configured to setup similar watch notifications. You can read more in the Reports API: Drive Activity Report overview.
