Learn how to automatically maintain consistent formatting, styles, and date formats when new Google Form responses are added to your Google Sheets.
Tired of Google Form responses messing up your carefully formatted Google Sheets? When new submissions come in, they often ignore the styles you’ve set, resulting in inconsistent looking data. Amit Agarwal, has created a guide that uses Google Apps Script to automatically maintain consistent formatting when new responses are added.
Google Forms does not apply any formatting when it adds new response data to a Google Sheet; this means that custom fonts, alignments, and date formats can be lost. Amit’s solution uses Google Apps Script to copy the formatting from the previous row to the new row, ensuring all data has a consistent look. The Apps Script runs automatically when a new form is submitted and identifies the new row and copies the formatting from the row above.
The guide provides a complete script with instructions for adding it to your Google Sheet and setting up a trigger. The script uses the copyFormatToRange method, which is designed specifically for copying only formatting between ranges. If you want to make sure your Google Sheet stays neatly formatted, check out Amit’s full guide to learn how to set up this handy automation for yourself.
Learn how to easily export your WooCommerce customers email, name and address to Google Sheets using Google Apps Script. The script will create a new tab in your Google Sheet and copy the data from the WooCommerce customers table.
If you are a WooCommerce store owner you might be interested in this solution by Amit Agarwal who provides a step-by-step guide on how to export WooCommerce customer data (email, name, address) to Google Sheets using Google Apps Script.
No more manual CSV exports or complex integrations, simply copy the provided script and follow the setup instructions. With this solution you can enhance your workflows, gain insights, and enhance your customer outreach. If you are not an existing WooCommerce user a solution for developers to keep in mind if you are seeking to optimise your Google Workspace add-ons customer experience.
Learn how to convert Google Slides into high-resolution PNG images using Google Apps Script. Choose between the Google Slides API and the Google Drive API based on your requirements.
Here’s a useful snippet from Amit Agarwal exploring two methods exploring how to convert Google Slides to PNG images with Google Apps Script. The first approach uses the Google Slides API to get the thumbnail images of the slides, fetch the blob of the image, and then upload it to Google Drive. However, this method has limitations including predefined fixed widths and requires two URL fetches.
The second approach uses the Google Drive API, which offers advantages like generating images with the same resolution as the original slide and requiring a single URL fetch. For both code snippets follow the source link.
Use Google Apps Script to create a custom menu that will work inside Google Sheets, Google Docs, Slides and Google Forms.
Tired of writing separate code for custom menus in Docs, Sheets, Slides, and Forms? This nifty Google Apps Script snippet by Amit Agarwal helps you build a universal menu that works across all these apps!
The solution is a small snippet which detects the context to see which of DocumentApp, SpreadsheetApp, FormApp, SlidesApp are available which then lets you switch to get the appropriate .getUI() method.
Check out the source post for the full code and streamline your add-on development!
Learn how to move uploaded files from Google Forms to a specific folder in Google Drive. You can also rename the files based on the form responses
Amit Agarwal, the brains behind Digital Inspiration’s Document Studio must have one of the most feature full Google Workspace Marketplace Add-ons. I also like Amit’s approach of sharing how you can solve problems like moving Google Form file uploads using Document Studio as well as providing an Apps Script snippet if you would like to manually code it.
In this example some setup is required to specify a folder ID and create an onFormSubmit trigger. After this the script then retrieves uploaded files from the form response, creates a subfolder named after the unique Response ID, and moves the files into that subfolder within the specified parent folder. This provides a structured and organized way to manage file uploads from Google Forms.
Follow the source link to read more about this script solution and about Document Studio.
This detailed guide explains how you access your passwords, API keys and other sensitive data stored in the Google Secret Manager with Google Apps Script.
API keys and other credentials are your digital passports to access valuable data and services from various online platforms. When you’re developing Google Apps Script projects that connect to using keys, it’s of paramount importance to handle these keys with care and prevent them from falling into the wrong hands. Mishandled API keys can lead to unauthorized access, data breaches, and potentially even financial consequences.
One way to store API keys in Google Apps Script is the built-in Properties Service. This service offers a mechanism to store key-value pairs attached to your Apps Script project. While the Properties Service offers a convenient way to store sensitive data, it comes with limitations:
Not True Encryption: The Properties Service stores data in a readable format. Anyone with sufficient access to your Apps Script project might be able to view the API keys.
Limited Access Control: It’s challenging to implement fine-grained access restrictions over who can see the stored keys within the Properties Service.
For a more secure and scalable approach you might consider using Google Cloud’s Secret Manager. As described by Amit Agarwal in this source post:
Google Secret Manager is a cloud service where you can store sensitive data such as passwords, database credentials, encryption keys or any other confidential information that you don’t want to hardcode in your application’s source code. You can also set up an expiration time for the secret and the Google Secret Manager will automatically delete the secret after the specified time.
You can read more about using the Google Cloud Secret Manager in Amit post, which also includes the setup and Apps Script code snippets to make it work. For some general best practices on key management, Google provide this support article.
Quickly rename files in Google Drive with Apps Script and Google Gemini AI. The script will automatically rename the files with a descriptive name based on the image content.
Amit Agarwal is celebrating 20 years of ‘Digital Inspiration’, one of my go to resources for Google Workspace tech tips, tutorials and how-to guides. This recent post from Amit is a great example of the types of solutions he regularly shares. This time Amit is looking at how the Generative AI capabilities of the Gemini API can be used to help rename images in Google Drive. To achieve this there Amit shares handy Apps Script snippets for finding images in a Google Drive folder, grabbing the thumbnail images as Base64 encoded strings before calling the Gemini Pro Vision API to get suggested filenames with the prompt:
Analyze the image content and propose a concise, descriptive filename in 5-15 words without providing any explanation or additional text. Use spaces instead of underscores.
For this solution an API key for Gemini Pro Vision is generated in Google AI Studio (formerly MakerSuite). There are still geographic restrictions on access to Google AI Studio, but given Apps Script runs on Google servers once you have a API key you can continue to use it without having to proxy a location.
Image credit: Amit Agarwal
Adding image capabilities to the GeminiApp Apps Script library has been on my TODO. Now I’ve got an example to play with it should be easier to do .. thank you Amit Agarwal.
Gmail will not render base64 images embedded inside HTML emails but with Google Apps Script, you can use blobs to send the base64 encoded images
Base64 is a way of encoding binary data (like images) as a string of text. This makes it possible to embed images in webpages and HTML emails without hosting images on a server. An issue however, particularly for email, is that not all email services like Gmail support base64 encoded images.
Amit Agarwal’s latest blog post discusses a workaround for Gmail which involves using Google Apps Script to convert the base64 images to a blob and then embedding the blob in the email. There are some nice techniques in this post for handling/searching text strings with regular expressions as well as converting them to a Gmail friendly format.
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.
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.
