In this report, I would like to introduce the sample scripts for using Microsoft Docs files with Document service, Spreadsheet service, and Slides Service of Google Apps Script.
Users have had the ability to edit MS Word, Excel and PowerPoint files directly in Google Drive for a number of years, Google making this the default behaviour in 2020. There aren’t currently any APIs or Apps Script services that enable you to edit these particular document types. This however hasn’t stopped Kanshi Tanaike for developing and sharing the MicrosoftDocsApp library which makes it possible to edit MS Word, Excel and PowerPoint files using the same methods as you would for Google Docs, Sheets and Slides.
The library is able to do this by converting the Microsoft files into the Google equivalent, before using the MS export options in Google Drive to replace the original files. This does create some limitations highlighted in the post. In particular, the original documents are overwritten and not directly edited which means any incompatible feature as part of the Microsoft to Google conversion will be lost. Regardless of these it’s a clever approach and might be useful for users who have to keep feet in both the Google and Microsoft worlds.
Gemini LLM, now a Vertex AI/Studio API, unlocks easy document summarization and image analysis via Google Apps Script. This report details an example script for automatically creating the description of the files on Google Drive and highlights seamless integration options with API keys.
In this blog post, Kanshi Tanaike shows how you can automatically create descriptions for files on Google Drive using the Gemini Pro API with Google Apps Script. The post includes a step-by-step guide on how to set up and use the Gemini Pro API by generating a key is Google AI Studio (formerly Maker Suite). There are geographic restrictions on Google AI Studio, but you can call Gemini Pro from a Google Cloud project with a little more setup (a previous post sharing Tutorial: Respond to incidents with Google Chat, Vertex AI, and Apps Script outlines a general approach for connecting Apps Script to Google Vertex AI services.)
The post includes a couple of examples showing how Gemini can be used to provide responses to both text and visual prompts. Gemini Pro is still in public preview and as Kanshi Tanaike highlights rate limiting will likely prevent putting these solutions into production just now. At this point hopefully there is enough to start experimenting with Google Workspace integrations to Gemini.
This is a sample script for copying the date object between Google Spreadsheets with the different time zones using Google Apps Script.
As I’ve previously mentioned working with dates, times and time zones can often be a bit of a headache. If you’d like to learn more about some of the challenges of dealing with ‘big balls of wibbly-wobbly, timey-wimey… stuff’ I recommend watching Comptuerphile’s Problem with Time & Timezones.
This post from Kanshi Tanaike highlights a couple of approaches for handling date/time objects in Google Apps Script when you are using Google Sheets.
Making the shared users input a value and upload a file without authorization of the scopes with a dialog on Google Spreadsheet.
It’s usually unavoidable when you are creating and sharing Apps Script projects that the user will be required to complete an authentication flow to approve access to the services you include in your script such as reading/writing to Google Sheets, Drive etc.
The process is reliant on OAuth scopes, which are identifiers that specify the level of access an application requests from a user’s Google Account data. They are essentially a way for developers to define the specific actions or data their application needs to access. When a user grants an application access to their Google Account, they are agreeing to allow the application to perform the actions or access the data specified by the scopes.
Sometimes you can restrict the ‘scope’, for example, usually for Sheets, Docs, Slides, and Forms where I need only permission for the current doc I will include the following documented comment to only require access to the doc that the script project is bound to:
/**
* @OnlyCurrentDoc
*/
There are some limitations when defining the scopes you need. For example if you would like a user to upload a document to Drive usually you would require the very broad https://www.googleapis.com/auth/drive scope which will prompt the user to ‘view and manage all of your Drive files’.
Understandably users may become nervous approving such a scope and in some cases Google Workspace Admins may prevent authentication for this type of scope for unverified/unconfigured applications.
There are alternative approaches to allowing users to execute Apps Script projects without having to approve scopes like Google Drive. There are clearly security considerations when you do this, so always proceed with caution.
This post from Kanshi Tanaike has some examples of how users can be prompted to upload files to Google Drive without authorising Drive access. The post includes two approaches, the first using a Web App which is pre authenticated to run as the user who has deployed the Web App, the other using a service account. The source post contains all you need to know include the code.
This report introduces the method for retrieving the Emoji reactions from the comments in Google Docs files (Google Documents, Google Slides, and Google Spreadsheets) using Google Apps Script.
Here’s a clever workaround by Kanshi Tanaike for retrieving comments with emoji reactions in Google Docs, Slides, and Sheets using Google Apps Script. The process to achieve this is a little convoluted in that Google Docs, Slides and Sheets are exported in Microsoft equivalent formats, then re-imported into Google formats. To remove some of the pain the post includes sample code snippets for achieving this, which can easily be adapted.
This report introduces the method for managing the histories of the Google Apps Script project.
On August 23, 2023, the project history has been implemented in the new IDE of Google Apps Script. In the current stage, the users can see the history of the previously deployed script version. … In the case of the classic IDE, the users had been able to see the previously saved script version regardless of the deployment and just the save of the script. This is not implemented in the new IDE.
In August 2023, Google updated the online Apps Script Editor adding a feature which lets you view previously deployed script versions and compare them to the current script version. A current limitation the history is limited to versioned deployments. As noted in this source post from Kanshi Tanaike, deployments require several steps and you have to remember to go through the process. To make the process easier Kanshi has published a ScriptHistoryApp library, which can be used to create a custom web interface for making your own Apps Script project version history. Perhaps more usefully you can also manage snapshots of your script projects by fetching a URL. This makes it easy for you to either regularly save a project on a timed trigger or an event based mechanism.
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!
Google Apps Script can be executed by time-driven triggers. This is one of the very important points for taking cloud computing. But, the scenarios using time-driven triggers are different for each user, and there are a lot of situations for using time-driven triggers. But, when a script for implementing time-driven triggers is developed, each script is different and complicated. In this report, I would like to introduce easily managing time-driven triggers using a Google Apps Script library.
In Google Apps Script time-driven triggers are a powerful way to automate tasks including Google Sheets. However, managing them can be difficult, especially if you have a lot of triggers and need triggers to run at different times and frequencies. The TriggerApp library from Kanshi Tanaike makes this a lot easier and this post includes a number of examples that are easy to copy/modify for your own script projects. The post covers the following scenarios:
Execute 2 functions at specific dates and times
Execute a function with a specific cycle between specific times between specific dates
Execute 3 functions with a specific cycle between specific times on weekdays
Send an email on a birthday every year
Execute specific functions on specific weekdays in a week
Execute 6 different functions every 10 minutes from “09:00:00” to “11:50:00” in order
Opening hours of Google Form from 09:00 to 17:00 on Weekdays
Opening Hours of Google Spreadsheet from 09:00 to 17:00 on Weekdays
Execute specific functions at 09:00 on Monday to Friday at the beginning of specific months
The source code for the library is on GitHub and includes documentation on all the methods it uses.
This is a sample script for retrieving and putting values for PDF Forms using Google Apps Script.
We’ve featured a couple of posts from Kanshi Tanaike on Pulse with solutions for handling/manipulating PDF Documents with Google Apps Script. This post on retrieving values from PDF Documents is just one in a series of recent contributions from Kanshi looking at how Google Apps Script can be used with PDFs:
In order to add and delete a text for the rich text in a cell, it is required to create a script while the current text style is kept. This is actually complicated. In this post, I would like to introduce the enriched management of rich text on Google Spreadsheet using Google Apps Script. In order to enrich the management of Rich Text using Google Apps Script, I created a library RichTextAssistant.
Some very clever work from Kanshi Tanaike which can help with the management of cell text formatting in Google Sheets. The RichTextAssistant Apps Script library included in the post has some nice methods for both handling and preserving cell text formatting. It’s worth spend a little time checking the various samples provided to see what is possible and how these might enhance one of your own script projects. If you are curious the source code for the library is also on GitHub and linked from the post.
