Correcting date formats in Google Sheets with Apps Script

Screenshot showing difference between American/British date formats

Search through Google Sheet data and correct the format of any date values to your choosing.

The following Google Apps Script is just one way you could search through data within a Google Sheet, pinpoint all date-values (no matter where they are), check their format (e.g. dd/mm/yyyy) and update it to a format of your choosing if necessary.

This function has been useful when American/British date formats have managed to make their way into a Google Sheet.

Source: The Gift of Script: Correct date formats in a Google Sheet

How to use Google Apps Script to automate your Gmail out-of-office message

As a Developer Advocate for Google Workspace, I live and breathe productivity tools. But even the most tech-savvy among us can have frustratingly simple oversights. My recurring pain point? Forgetting to turn on my Gmail vacation responder to automatically notify people of my absence before heading out of office.

Google Workspace Developer Advocate, Chanel Greco, has shared a clever solution to automate their Gmail out-of-office notification using Google Apps Script. Faced with the recurring problem of forgetting to set up the out-of-office notification, Chanel crafted a script that does the job based on Google Calendar events marked as ‘outOfOffice’.

Chanel’s project not only solves a personal problem but also demonstrates the benefits of Google Apps Script for task customization and productivity. By harnessing the power of Workspace APIs, you can create innovative solutions to automate routine tasks and streamline workflows.

Follow the source link to read more…

Source: How I Used Apps Script to Never Forget My Gmail Vacation Responder Again

Build a Google Drive Add-on with Gemini to rename Untitled Google Docs – Name with Intelligence

Google Workspace Add-on for Google Drive, which uses AI to recommend new names for the selected Doc in Google Drive by passing the body of the document within the AI prompt for context.

Tired of staring at “Untitled document” in Google Drive? At Google Cloud Next ’24 and I/O 2024, the Google Workspace DevRel team showcased new Apps Script samples utilizing the Gemini API for AI-powered functionality.

One such sample, “Name with Intelligence” by Charles Maxson, helps developers overcome this common hurdle. This Google Drive add-on leverages the Gemini API to suggest relevant titles for your documents, saving you valuable time and effort.

This innovative Google Drive add-on tackles the common struggle of naming untitled documents. It utilizes the Gemini API to analyse the content of your Google Doc and suggest relevant titles based on its understanding of the text. This not only saves you time brainstorming titles, but also ensures your documents are clearly named for easy searchability later.

Want to see it in action? Check out this clip from Charles’ TU5.8 – Apps Script and Gemini Next ’24 Redux presentation.

Ready to give “Name with Intelligence” a try?

Head over to the GitHub repository to explore the source code.

For those interested in my GeminiApp library, you can also find the sample here.

Take Your Development Further

This is just a glimpse of what’s possible with the Gemini API and Apps Script. With a little creativity, you can develop your own AI-powered Google Workspace add-ons to streamline your workflows and boost your productivity.

I encourage you to experiment and create innovative solutions that enhance your Google Workspace experience!

Source: apps-script-samples/ai/drive-rename/ at main · googleworkspace/apps-script-samples

Deploying an Apps Script Library Part 7: Contributing

Open-source made me a better developer, and can do the same for you.


This is our final stretch! We’ve navigated through building an Apps Script library, and now it’s time to discuss how you can contribute. Here’s a quick rundown of what we’ll cover:

  1. GitHub Flow: Learn to fork the repository, create a feature branch, and make a pull request.
  2. Guidelines: Tips on ensuring your pull request gets accepted.
  3. Code of Conduct: We won’t delve into this, as respectful behavior is the default expectation.
  4. License: Our library uses the MIT License, which is pretty much the one that lets you do anything you want with the code.
  5. Issues: I will post issues that I consider a priority, but feel free to tackle any of them.
  6. Process: Follow the steps outlined in our file.

As a reminder, here are the links to the repo and the NPM page.

But first, let’s talk about what you should do before you open a pull request.

Source: Deploying an Apps Script Library Part 7: Contributing

Deploying an Apps Script Library Part 6: Deploying on NPM

Everyone says use Google Apps Script libraries for easier maintenance — I found a better way. Here’s why NPM modules might change the game for you too.

Last time, we covered how to deploy our library as a native Google Apps Script (GAS) library using Vite, GitHub Actions, and clasp. This setup is efficient, but there’s a notable drawback to using Apps Script libraries.

According to the official guide: Warning: A script that uses a library doesn’t run as quickly as it would if all the code were contained within a single script project. Although libraries can make development and maintenance more convenient, use them sparingly in projects where speed is critical. Because of this issue, library use should be limited in add-ons.”

Apps Script is not known for its speed, being a free runtime. Slowing it down further by using native GAS libraries is generally not advisable. Therefore, I prefer to avoid native GAS libraries whenever possible.

Instead, it’s much more efficient to bundle your dependencies directly into your source code, similar to how it’s done in Node.js. This is where NPM modules and Vite come into play.

In this post, we’ll cover:

  • Which NPM modules are compatible with Google Apps Script and which ones are not.
  • How to use NPM modules in your Apps Script projects.
  • How to build and publish a module on NPM.

Let’s start with compatibility.

Source: Deploying an Apps Script Library Part 6: Deploying on NPM

A comprehensive look at key value store options in Google Apps Script

A comparison of key-value store options in Google Apps Script, including PropertiesService, CacheService, Firestore, and Sheet Developer Metadata.

Justin Poehnelt has shared a comprehensive look at key-value store options in Google Apps Script. Key-value stores are useful for storing data like user preferences or frequently accessed data, which can also be shared between script executions.

As part of Justin’s post as well as looking at the built-in store options, PropertiesService and CacheService, there is also a look at scenarios where you might want to consider alternatives like Firestore.

The choice of key-value store depends on factors like expiration needs, number of items, value size, access control requirements, latency sensitivity, and cost considerations. Justin’s post provides a thorough analysis of each factor, including latency comparisons.

Head over to the source link to get all the details!

Source: Key Value Store Options in Google Apps Script | Justin Poehnelt

How to easily add the same Custom Menu to Docs, Sheets, Slides and Forms with Google Apps Script

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!

Source: How to Add a Universal Custom Menu to Multiple Google Workspace Apps – Digital Inspiration

Google Forms automation with AppSheet, automatic emails and reminder with ease

Power of Google Forms with Power of AppSheet make Forms automation super easy and efficient.

Following our TU Special on building AppSheet automations using the new Google Forms integration, Stéphane Giron shared a Medium post detailing a couple of example use cases. In the post, Stéphane shares some intermediate examples that go beyond the basics, with the potential for conditional routing and advanced automations.

If your automations are not being triggered by Google Form submissions, Pablo Felip’s tip is it could be because your Google Workspace Admin has turned on AppSheet Core Security.

Source: Google Forms automation with AppSheet, automatic emails and reminder with ease

Move files uploaded with Google Forms to specific folders in Google Drive with Google Apps Script

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.

Source: How to Move Files Uploads from Google Forms to Specific Folders in Google Drive – Digital Inspiration