AppsScriptPulse

Add-on, Library, Drive, Intermediate

ReDriveApp: A new Google Apps Script library to replace DriveApp and restricted scopes 

Tags:
| | 0 Comments |

Apps Script class that provides equivalent methods offered by the built-in DriveApp, but that does not require use of full ‘/drive’ OAuth scope (which is a “Restricted” scope”). Instead, uses only these Recommended (non-sensitive) and/or Sensitive scopes

When you use OAuth 2.0 to get permission from a Google Account to access their data, you use strings called scopes to specify the type of data you want to access on their behalf. For Google Workspace Add-on developers wanting to distribute your app in the Google Workspace Marketplace one consideration is only using the scopes required for your app. In the case of Google Drive a number of the scopes are classified by Google as “restricted”. To use “restricted” scopes there is an enhanced verification process, which requires Cloud Application Security Assessment (CASA) by an independent security assessor, which come at a cost and can be time consuming.

ReDriveApp is a new Apps Script community library published by Dave Abouav which makes it easier for developers to use reduced recommended scopes for integrating with Google Drive. As explained by Dave:

The built-in DriveApp service is an easy and powerful way to interact with Google Drive in your Google Apps Script projects, which is why so many developers make use of it. It’s one downside is that it forces your project to use the full ‘/drive’ OAuth scope, which is a “Restricted” scope.

In many cases though, projects don’t really need the full /drive scope for common tasks, and the Recommended /drive.file scope is sufficient. This allows your project to create new files, and open existing files if authorized by the end-user via the Drive Picker. Using it also avoids the aforementioned security review, and is less alarming to users when authorizing your app.

ReDriveApp is still work-in-progress and not an official Google project. There are a number of methods that still need to be implemented, but the project is open source and can be contributed to on GitHub. If you are planning or have already developed a Marketplace Add-on that has stalled due to enhanced verification for restricted scopes it could be worth looking at and contributing to this library.

Source: GitHub – ReDriveApp

Library, Solutions, Intermediate

FetchApp: Open-source Google Apps Script library to enhance UrlFetchApp

Tags:
| | 0 Comments |

Google Apps Script is often used to pull data from various services via HTTP requests. However, these requests sometimes fail due to network or service issues. The default behavior of UrlFetchApp is to throw an exception, which you have to catch. Otherwise, the script execution will be interrupted. We often need more: send the request again instead of failing. There is no built-in way to do retries in Apps Script. Solution – To solve this problem and not copy-and-paste code snippets from project to project, I created FetchApp – an open-source Google Apps Script library

This looks like a useful little library which puts a nice wrapper around the UrlFetchApp service. The wrapper includes options for: retries, delays (including exponential backoff), custom callbacks, code hints and automatic logging. The supporting post includes lots of documented code snippets for using the library which should make it easier to implement. A very handy drop in replacement if you are having to communicate with more sensitive API endpoints. The code is also on Github if you want to take a look at how the library is coded.

Source: FetchApp: UrlFetchApp with Retries

Library, Solutions, Advanced

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

Tags:
| | 0 Comments |

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

Library, Solutions, AI, Gmail, Sheets

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

Tags:
| | 0 Comments |

 

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

Library, New IDE, Solutions

Creating a custom Google Apps Script project version history 

Tags:
| | 0 Comments |

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

Library, Solutions, Intermediate

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

Tags:
| | 0 Comments |

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…

Library, Solutions, Drive, Intermediate

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

Tags:
| | 0 Comments |

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

Library, Intermediate

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

Tags:
| | 0 Comments |

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

Library, Solutions, AI, Advanced, No Code

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

Tags:
| | 0 Comments |

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

Library, Solutions, Docs, Sheets, Slides, Intermediate

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

Tags:
| | 0 Comments |

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