AppsScriptPulse

How Apps Script Became the Ultimate LLM Fine-Tuning Tool

If you have domain-specific knowledge that you want an LLM to leverage, you probably have a use case for fine-tuning. Fine-tuning can significantly improve how well the model understands and responds to your queries, whether it’s legal documents, medical texts, financial reports, or niche industry data.

The most crucial step in this process is structuring your data correctly. If your dataset is well-organized and formatted properly, the rest of the workflow becomes much more manageable. From there, it’s just a matter of setting up a few configurations and automating parts of the process with Apps Script. That’s where things get interesting and surprisingly efficient.

Source: How Apps Script Became the Ultimate LLM Fine-Tuning Tool

Rhino Runtime Retirement: Action Needed for Apps Script Projects

As of February 20, 2025, the Rhino runtime is deprecated. Scripts running on Rhino will continue to function until January 31, 2026, after which they will no longer execute. Please migrate your scripts to the V8 runtime before this date. Refer to Migrate scripts to the V8 runtime.

Attention Google Workspace Developers!

A recent email to Google Workspace administrators announced the upcoming retirement of the Rhino runtime for Apps Script. As also highlighted in the Apps Script release notes, all Apps Script projects still using the Rhino runtime must be migrated to the V8 runtime by January 31, 2026, to avoid execution issues.

What this means for Workspace Developers:

If you are a Workspace developer, even if you don’t have an administrator role, it’s crucial to check your Apps Script projects for Rhino runtime usage. The email to administrators included a CSV file listing affected projects, with details such as:

  • Script URL, Script ID, Last Updated: Identifies the specific script.
  • Creator Email, Owner Email: Identifies the script’s originators.
  • Incompatibility Reason: Explains why the script needs migration.
  • Execution Count: Shows how often the script is used.
  • Additional Information: Includes opt-out status, library dependencies, etc.

Suggested Actions for Developers:

  • Non-Admins: You might want to contact your Workspace administrator to find out if any of your or your colleague Apps Script projects are on the list of affected scripts and ask for guidance on the migration process.
  • Third-Party Developers: You may want to proactively reach out to your customers to inform them of the upcoming changes, offer assistance with the migration, and ensure their scripts are updated before the deadline to prevent any disruption in their services.
  • Workspace Admins: Review the CSV file to identify your affected scripts, prioritize migrating scripts with high usage first to minimize user disruption, and understand the specific issues that need to be addressed in each script.

Important Considerations:

Google provides a migration guide and support resources to assist with the transition. In particular, it is worth noting that:

  • For Apps Script web apps and Google Workspace Add-ons “[You] must create a new version of the script with the V8 adjustments. To make the V8 version available to users, you must re-publish the script with this version.”
  • When updating standalone scripts to run on the V8 runtime, “you need to provide users at least view access to the script in order for the script’s triggers to work properly.”

It’s also important to note that while the V8 runtime generally offers performance advantages, there have been some reports of specific instances where performance with JDBC connectors might not be as efficient as in the Rhino runtime.

So don’t delay, start migrating your Apps Script projects today!

Source: Google Apps Script release notes  |  Google for Developers

Streamline Your Google Sheets: Automate Row Archival with Google Apps Script

Archive Google Sheet Data with Google Sheets API Advanced Service in Google Apps Script

Keeping your Google Sheets organised can be a chore. A recent tutorial by Scott Donald shows you how to automatically archive old rows based on multi-column parameters using Google Apps Script and the Advanced Sheets API, making data management easier and more efficient. This post highlights Scott’s approach to scripting a solution, including how to set up the Sheets API, the main components of the script, and potential applications.

As always Scott packs in a lot of useful tips and guidance; in particular, the tutorial highlights how to archive data using just five API calls, potentially making it faster than the standard SpreadsheetApp approach. Scott also details how to modify the script to suit different processes and includes details on his SsReq class. It provides a structured way to perform common operations like retrieving data, finding rows based on criteria, copying rows, appending rows, and removing rows, making it a great addition to your personal Apps Script toolbox.

For detailed explanations, code examples, and helpful tips, consult the complete tutorial.

Source: Creating a Google Sheets Row Archiver with Google Sheets API Advanced Service and Apps Script – Yagisanatode

How to Assign Google Workspace Licenses Using Google Apps Script

Assign Google Workspace licences for individuals without using the Admin Console, via a simple Form submission.

Screenshot of the Google Form title used for submission

Screenshot of the Google Form title used for submission

The following Google Apps Script is designed to automate the assigning of a Google Workspace (e.g. Education Plus) licence in the Google Admin Console, for individual accounts/users by providing them with a quick Google Form to submit.

This saves having to manually go into the Google Admin Console and assign a licence. All the user has to do is tick the box on the Google Form, they will receive an automated confirmation email and their new licence will be applied within 24 hours.

You can refer to the Google product and SKU IDs webpage if you need to assign a different licence type. There is some basic checking to see if an account/user is an ‘Administrator’ and it will therefore prevent them from getting a licence.

Source: The Gift of Script: Assign Google Workspace licences for individuals without the Admin Console

Automating Data Extraction with the Gemini API Controlled Generation and AppSheet’s New Gmail Integration

Tired of manually processing invoices? I recently built a demo that automates this tedious task using the power of Gemini, AppSheet’s new Gmail integration, and a custom Apps Script library. Let me show you how it works!

Here’s the setup:

  • Gmail Integration: AppSheet’s new Gmail integration allows you to trigger automations based on incoming emails. I’ve set it up to monitor a specific Gmail label called “Invoices”.
  • Apps Script Library: Using my “GeminiApp” (available on Github) which simplifies the interaction with Google’s Gemini AI models directly from Apps Script. This library handles the heavy lifting of making API calls and managing responses.
  • Controlled Generation: Gemini’s “Controlled Generation” feature lets me define a JSON schema that structures the AI’s output. This is key for extracting invoice data in a consistent, machine-readable format.

The Workflow:

  1. Invoice Received: When an invoice email arrives and is labelled “Invoices”, AppSheet’s Gmail integration kicks in.
  2. Automation Triggered: AppSheet triggers an automation that calls a custom Apps Script function called jsonControlledGeneration.
  3. Data Extraction: The jsonControlledGeneration function uses the GeminiApp library to send the email body to Gemini with a predefined JSON schema for invoice data.
  4. Structured Output: Gemini processes the email content and extracts the relevant invoice details (e.g., invoice number, supplier name, date, amount) in a JSON format that adheres to the schema.
  5. Downstream Processing: The structured JSON output can then be easily returned to the AppSheet automation for further actions, such as automatically populating your data table, updating a database, or triggering a payment process.

If you would like to try this yourself, instructions are provided below.

Want to try it yourself?

To set this demo up you will either need a Google AI Studio API key or a Google Cloud project with Vertex AI enabled. Information on both these setups is included in the GeminiApp Setup Instructions. Once you have these you can follow these steps:

  1. Open the GeminiApp Controlled Generation Google Apps Script project and click ‘Make a copy’ from the Overview page
  2. Open the Invoice Tracker template and click ‘Copy and Customize’, then click OK on the Error Creating App: Apps Script function is invalid error
  3. Navigate to appsheet.com and from your ‘recent’ apps  open the ‘Invoice Tracker’ app
  4. Open Automations and for the ‘New Invoices’ event under the Gmail Event Source, click Authorize, then
  5. In the ‘Add a new data source’ enter a name e.g. Invoices Trigger, click the Gmail button and follow the authentication steps
  6. Once complete in the AppSheet Automation settings select your Gmail account and a Label to watch
  7. In the Process section click on the GeminiApp task and click on the blue file icon, then select your copied version of the Apps Script project and click Authorize
  8. Once Authorize, from the Function Name select jsonControlledGeneration

To test this app, you can copy and send this example invoice.

Step 7: Click on the blue file icon, then select your copied version of the Apps Script project

The Power of Controlled Generation

Controlled Generation is a powerful way for extracting information from unstructured data like emails. By defining a JSON schema, I can specify exactly what information I want Gemini to extract and how it should be structured. This ensures that the output is consistent and reliable, eliminating the need for manual cleanup or post-processing.

Here’s an example of a simple JSON schema for invoice data:

 const schema = {
    "type": "object",
    "properties": {
      "Invoice Reference": {
        "type": "string",
        "description": "Unique identifier for the invoice"
      },
      "Supplier Name": {
        "type": "string",
        "description": "Name of the supplier"
      },
      "Invoice Date": {
        "type": "string",
        "description": "Date the invoice was issued",
        "format": "date"
      },
      "Due Date": {
        "type": "string",
        "description": "Date the invoice is due",
        "format": "date"
      },
      "Invoice Amount": {
        "type": "number",
        "description": "Total amount due on the invoice"
      },
      "Notes": {
        "type": "string",
        "description": "Additional notes related to the invoice",
        "nullable": true
      }
    },
    "required": ["Invoice Reference"]
  }

Creating JSON Schemas with Gemini

Creating JSON schemas can seem a bit daunting at first, but Gemini can actually help you with that too! If you have sample data in a Google Sheet, you can use the Gemini Side Panel to generate a schema automatically. Just highlight the data and ask Gemini to create a JSON schema for you. You can even provide a description for each property to make your schema more understandable. Below is a prompt you can use in the Gemini Sheet Side Panel to generate a schema for your own data:

I'm using Controlled Generation with the Gemini API as described in the document https://drive.google.com/file/d/1ETKHlEUDQzJ-f2fmAzsuDjcwdt1D7R2y/view?usp=drive_link

I need help creating a JSON schema to capture data from a screen.

Could you generate a JSON schema suitable for using Controlled Generation with the Gemini API? I want to extract specific information from what's displayed on my screen.

Here are my requirements:

* **Comprehensive Schema:** The schema should be designed to capture a variety of relevant data points from the screen.  
* **Detailed Descriptions:** Please include a clear and concise \`description\` for each \`property\` in the schema. This will help me understand the purpose of each field.  
* **Format Specification:** If any columns contain date or datetime data, please use the \`format\` field to specify the appropriate format (e.g., "date", "date-time"). This is crucial for accurate data parsing.  
* **Output Example:** Please provide the schema in the following format:

```
const schema = {
  description: "Description of the data",
  type: "array", // or "object", depending on the structure
  items: { // If type is array
    type: "object",
    properties: {
      propertyName: {
        type: "string", // or other appropriate type
        description: "Description of the property",
        format: "date", // or "date-time", if applicable
        nullable: false, // or true
      },
      // ... more properties
    },
    required: ["propertyName"], // If any properties are required
  },
  properties: { // If type is object
      propertyName: {
        type: "string", // or other appropriate type
        description: "Description of the property",
        format: "date", // or "date-time", if applicable
        nullable: false, // or true
      },
      // ... more properties
    },
    required: ["propertyName"], // If any properties are required
};
```

Limitations and future developments

While the beta Gmail integration in AppSheet marks a significant new feature, it’s important to note a current limitation is support for processing email attachments. Currently, the integration focuses on metadata such as sender name, subject, and message body, but the AppSheet team have acknowledged attachment support will be added in the near future.

Looking ahead, at Google Cloud Next 2024 the AppSheet team announced an upcoming “Gemini Extract” feature, currently in private preview. This feature intends to include a native Gemini ‘controlled generation’ feature which would let app creators select the data fields they would like populated from sources including images and text. This should be a more intuitive approach to data extraction, directly integrating Gemini capabilities into AppSheet. The Next video includes a Google URL to sign up to the Gemini Extract feature https://goo.gle/appsheet-previews?r=qr

Summary

The Invoice Tracker example hopefully highlights the opportunity for streamlined solutions with data extraction with AppSheet’s Gmail integration, Gemini, and Apps Script.The GeminiApp library also  simplifies the integration of Google’s Gemini AI models into Google Workspace, providing developers with tools to create sophisticated AI-powered applications.

Using the structured JSON output with Controlled Generation can help AppSheet creators by making it easier to ensure you get the data back in a suitable format including the type of data you need, such as dates. With the GeminiApp library, rapid prototyping is achievable, in the ‘Invoice Tracker’ example I was able to get a functional prototype up and running in under 30 minutes.

AppSheet’s Gmail integration, generally available to all AppSheet and Google Workspace Core users, can trigger automations directly from incoming emails without requiring app deployment. Combined with Apps Script functions this opens the door to some powerful opportunities for AppSheet creators. Integrating Gemini-powered AI extraction with AppSheet and Apps Script provides an innovative solution for automating data extraction from emails. By taking advantage of these capabilities, citizen developers can create efficient and user-friendly solutions.

AppSheet’s New Gmail Integration: Bridging the Gap Between your Inbox and Applications

We’re pleased to announce the public preview of Gmail integration with AppSheet – something we know has been a longstanding community request. With this preview launch, your Gmail inbox can seamlessly connect to the full breadth of downstream processes AppSheet automation already offers.

AppSheet has introduced a Gmail integration in public preview, offering a workaround for situations including third-party applications that lack direct integration with Google Workspace via add-ons or APIs. With this new feature users can now connect their Gmail inboxes to AppSheet, enabling incoming emails to trigger various automations.

How it Works

Users can link their AppSheet apps to authorized Gmail accounts and select specific labels in their Gmail inboxes for monitoring. When an email arrives with a designated label, AppSheet receives information about the email, including the sender, subject, and body. This information can then be used to automate various tasks and processes within the AppSheet platform.

Things to Note

Currently, the beta release does not include support for attachments, but AppSheet is working on adding this feature for the full release later this year. Also, Gmail cannot be used as a full data source in AppSheet. To save Gmail messages in an app table, users need to create a process to add the email data.

Availability and Feedback

The Gmail integration is rolling out to all AppSheet users soon as part of the preview and the AppSheet team is encouraging users to provide feedback on this new integration. To read more and provide feedback follow the source link

Source: Gmail Integration in AppSheet: Now in Public Preview! (Beta)

Bulk Image Generation with Imagen 3, Sheets, and Apps Script

Discover how to combine Imagen 3 and Google Sheets for rapid image creation. Automate your workflow and generate visuals in bulk.

Have you ever wished you could create a bunch of images without the hassle of typing in each prompt one by one? Well, Stéphane Giron has shared a clever solution using Google’s Imagen 3, Google Sheets, and Apps Script to streamline this process. While Gemini for Workspace is rolling out to more users and includes Imagen 3 for image creation in apps like Slides and the Gemini side panel, it doesn’t offer an easy way to create images in bulk. That’s where this method comes in. Instead of making images one at a time, this approach allows you to generate multiple images at once, saving you time and effort.

To get started with this method, you’ll need a Google Cloud Project and creating a service account (see the Build an AI slides reviewer with Gemini tutorial for instructions on setting this up). Once that’s set up, you create a Google Sheet with a column for your text prompts, and another for the links to the generated images. The images are automatically saved into a specific folder in your Google Drive.

The real magic happens with Apps Script. It uses Vertex AI to connect with the Imagen 3 model. The script reads each prompt from your sheet, sends it to Imagen 3 to create an image, and then stores the image in Google Drive. It also helpfully adds the image’s link back into the Google Sheet.

Check out the source post for the required code and instructions.

Source: Imagen 3 + Google Sheets + Google Apps Script: The secret to effortless bulk image generation

Keep Your Google Sheet Data Tidy: Auto-Formatting Form Responses with a Little Apps Script

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.

Source: How to Automatically Format Google Form Responses in Google Sheets

Gassypedia January 2025 Update: Apps Script Ecosystem Growth

Quarterly update on the shared bigquery dataset all about the Apps Script developer ecosystem on Github, with reports available on looker.

Bruce Mcpherson’s latest Gassypedia update offers a look at the Apps Script ecosystem, using publicly shared code on GitHub. The report analyses data from over 3100 developers and nearly 100 million files across 200,000 repositories. You can read the full report from the source link at the bottom but here are some key points:

  • Growth: The Apps Script community is still expanding, but at a slightly slower pace than before.
    • The number of developers increased by 4%.
    • Shared repositories grew by 3%.
    • Manifests increased by 2%.
  • Add-ons: There’s a 13% increase in Add-ons shared on GitHub.
    • Chat Add-ons are now most popular, surpassing Sheets.
    • Chat Add-ons increased by 18%, and Sheets Add-ons by 10%.
    • Calendar Add-ons saw a slight decrease.
  • Code Sharing: While the number of Apps Script developers and related content has increased, the total number of all types of repositories and files associated with them has decreased by almost 5%. However, the percentage of those repositories that are Apps Script related has increased.
  • Clasp: 25.2% of manifests showed Clasp usage, a 1% increase.
  • OAuth: 851 repos and 1008 manifests use the auth/script_external_request scope, a 6% increase.
  • Advanced Services: Drive is the most popular advanced service, used in 499 manifests.
  • Web Apps: 53% of web apps are public, and 81% are executed as the user deploying. 6% of web apps are created in Japan.
  • Libraries: oauth2 is the most popular library, with 236 users.

Key Takeaways

  • Apps Script is still a growing platform, with many developers sharing their work.
  • The rise of Chat Add-ons indicates the maturing of the platform for Google Workspace users.
  • More developers are sharing Apps Script projects on Github.

Explore the Data

Bruce’s full report is on Looker, with the data available on BigQuery. You can use this data for your own research and share any findings with Bruce.

Source: Gassypedia – January 2025 update – Desktop liberation

A Date-Checking Google Apps Script for Your Google Forms

Google Apps Script to check that a date submitted via a Google Form is both in the future and falls on a Wednesday.

Google Form question asking for a date to be entered

Google Form question asking for a date to be entered

The following Google Apps Script is designed to check that a date submitted via a Google Form is both in the future and falls on a Wednesday. This was developed as part of a newsletter submission process to help validate information and inform a user when they had not followed the instructions. It has been developed for UK dates and may need adjusting for your own time zone.

Source: The Gift of Script: Check date in future and a Wednesday