AppsScriptPulse

‘Editing’ Microsoft files (Word, Excel, and PowerPoint) with Google Apps Script

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.

Source: Use Microsoft Docs Files (Word, Excel, and PowerPoint) with Document Service, Spreadsheet Service, and Slides Service of Google Apps Script

GenAI for Google Workspace: Exploring Gemini API Function Calling with Google Apps Script — Part 3

 

Using Google Gemini API with Google Sheets to create personalized mail merges. An exploration in generative AI ‘function calling’

Google latest generative AI solution, Gemini, includes the capability to declare functions that the LLM can use in it’s response. The response includes the name of the function and the parameters the script needs to run the function.

The function isn’t executed by the LLM, but run with your code, which creates really interesting opportunities for Google Apps Script solutions. In particular, given user identity and authorisation is an integral part of Apps Script and how it integrates with other Google services it means solutions like personalised mail merges can be created in a couple of lines of code.

Follow the source link to find out more about function calling and exploring Gemini’s capabilities with data in Google Sheets.

Source: GenAI for Google Workspace: Exploring Gemini API Function Calling with Google Apps Script — Part 3

Creating a custom Google Apps Script project version history 

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.

Source: Managing History of Google Apps Script

GenAI for Google Workspace: Exploring the PaLM 2 API and LLM capabilities in Google Sheets with Google Apps Script — Part 1

This post has covered how you can quickly copy MakerSuite code examples to run them in Google Apps Script. To make the iterative process easier I’ve created GenerativeLanguageApp so that once you’ve created/saved an API key, you can drop these code snippets into your Google Apps Script project:

Generative AI (GenAI) is a rapidly developing field that has the potential to revolutionize many industries, including the way we work. Google has developed a number of LLMs that are generally available to developers, including foundation models trained for text, chat and code which are accessible as part of the PaLM 2 API. To help developers explore the capabilities of these models, Google has created the MakerSuite site.

In this post I share GenerativeLanguageApp, a Google Apps Script helper class which makes it easy to quickly copy MakerSuite code examples to run them in Google Apps Script. This is a great way to start experimenting with LLMs and seeing how they can be used in Google Workspace.

In the next part of this series, I’ll explore some of the capabilities of when using the PaLM 2 API with data from Google Sheets. Stay tuned!

Source: GenAI for Google Workspace: Exploring the PaLM 2 API and LLM capabilities in Google Sheets with…

PDFApp and many recipes for ‘cooking’ PDFs with Google Apps Script

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:

  • Export pages from a PDF
  • Get metadata of a PDF
  • Update metadata of a PDF
  • Reorder pages of a PDF
  • Merge PDF files
  • Convert PDF pages to PNG files
  • Get values from PDF form
  • Set values to PDF form
  • Create PDF form using Google Slide as a template
  • Embed objects into a PDF

The source code for PDFApp is also on GitHub if you want to dig further.

Source: Cooking PDF over Google Apps Script

Easily managing time-driven triggers using Google Apps Script and the TriggerApp library

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.

Source: Easily Managing Time-Driven Triggers Using Google Apps Script

Introducing ChatGPTApp, a new library for Google Apps Script and ChatGPT (and a new way to generate personalised mail merges)

Last month, OpenAI announced Function calling, “a new way to more reliably connect GPT’s capabilities with external tools and APIs”. Super useful ❤️ and we decided to integrate that with Google Apps Script.

Following on from yesterday’s Pulse post which highlighted Ben Collins reflections on ‘What can AI do for you as a Google Sheets user? Is the hype justified?’, here is a contribution from another Apps Script expert, Romain Vialard, announcing a ChatGPT library for Google Apps Script.

The source post highlights a number of examples, including the standard ‘prompt’ calling. Where this post gets very interesting is integration with Open AI’s function calling features:

In an API call, you can describe functions to gpt-3.5-turbo-0613 and gpt-4-0613, and have the model intelligently choose to output a JSON object containing arguments to call those functions. The Chat Completions API does not call the function; instead, the model generates JSON that you can use to call the function in your code.

To illustrate this below is a screenshot of a very slightly extended example, which has a Google Sheet with the headings email, name and tip topic. The functions are described to ChatGPT and in the case of sendMessage() the parameters it requires. The ChatGPTApp library handles the functions and in the case of the getContactsList() uses it to pass the Google Sheet data for ChatGPT to format a JSON response to correctly call the sendMessage() function.

I found it took me a while to start understanding what is going on and would recommend experimenting with the library and sample code to get a sense what is possible.

Source: Introducing ChatGPTApp, a new library for Google Apps Script

Easily merge data from Google Sheets into Google Docs and Slides with the TemplateApp Google Apps Script

This report introduces the method for easily processing the template of Google Documents and Google Slides using Google Spreadsheet as a database using Google Apps Script. Google Spreadsheet is used as a database. Google Documents and Google Slides are used as templates. The simple method for creating new Google Documents and Google Slides using the database and the templates is introduced.

Latest Apps Script magic from Kanshi Tanaike, this time they turn their addition to an easy way to use data from Google Sheets as a ‘document merge’ for templates created in Google Docs and Google Slides. This is achieved by using the TemplateApp Apps Script library, which has lots of useful features including simply methods for sheetRangeToDocuments() as well as the ability to embeded and fixed width images from placeholders. There are some llimitations documented in the GitHub repo, as well as examples for all the methods available in the library.

[In other ‘merge’ news Google have announced that you can now use Google Sheets with Google official mail merge in Gmail]

 

Source: Easily Processing Templates of Google Documents and Google Slides using Google Spreadsheet with Google Apps Script

New Google Apps Script library to convert files with the Drive API (including OCRing image file formats)

Apps Script library to convert between file types, including OCR for image to documents, with a huge repertoire of conversion combinations.

The Drive API offers a whole range of conversions between mimeTypes, but it’s a little fiddly to figure out exactly how. This library takes a file and an a desired output format and converts it for you. Sometimes, there’s not a direct route – for example if you need to convert a word file to a pdf, it first needs to get converted to a Google Doc, then to a Pdf. This library automatically works out and actions any intermediate conversions required.

For those who are unfamiliar MIME types (mimeTypes) is a standard way of identifying a file’s content type, such as text, image, or audio. The MIME type for a file is usually used by applications to determine how to open and display the file. Google Drive is able to convert various files, such as a MS Word document to Google Docs.

Sometimes there isn’t a direct route and this clever library from Bruce Mcpherson can help alleviate the pain. The library works by first checking if there is a direct route between the input and output file formats. If there is, it simply converts the file. If there is no direct route, the library will use the Drive API to first convert the file to a format that can be converted to the output format. For example, if you need to convert a Word file to a PDF, the library will first convert the Word file to a Google Doc, and then convert the Google Doc to a PDF.

Knowing what mimeType to use can be a bit of a challenge but you can also use the library to give a list of known types, or if you prefer a more readable list of Google Drive export MIME types.

Source: Convert any file with Apps Script – Desktop Liberation

Management of rich text cell formatting in Google Sheets with Google Apps Script

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.

Source: Enriched Management of Rich Text on Google Spreadsheet using Google Apps Script