Refresh

This website pulse.appsscript.info/p/tag/kutil-org/ is currently offline. Cloudflare's Always Online™ shows a snapshot of this web page from the Internet Archive's Wayback Machine. To check for the live version, click Refresh.

AppsScriptPulse

From Vague Vibes to Structured Data: “Vibe Scraping” with Google Apps Script and the Gemini API


Nine years ago, I wrote an article describing how to scrape data from the internet in about five minutes. It featured a Google Apps Script library that allowed you to specify what to download from a webpage by identifying the text surrounding your target information. This became one of my most-read articles, and the library itself saved me a significant amount of time.

With the advent of large language models like Gemini, this entire paradigm is shifting. Just as “vibe coding” describes a more intuitive approach to programming, I’d say we’re now entering an era of “vibe-scraping.”

Using data from the web has long been a staple. But what if you could just describe the information you want and have an AI fetch it for you?

Google Developer Expert, Ivan Kutil, introduces a modern approach he calls “vibe scraping.” In his latest post, he demonstrates a powerful method that combines Google Apps Script with the Gemini API to pull structured data from any webpage directly into a Google Sheet.

To understand the power of this technique, Ivan set out to solve an old problem. He had a Google Sheet with a list of movies, each with a URL pointing to its page on ČSFD (a popular Czech-Slovak film database). His goal was to automatically populate columns with details like genre and rating by extracting the data directly from those linked pages.

At the heart of his solution is a new, experimental Gemini API feature called the URL Context tool. As the documentation notes, this tool is surprisingly flexible: it “can then retrieve content from the URLs and use that content to inform and shape its response.”

The tool can be used in two main ways:

  1. URL Context “By Itself”: In this mode, the tool directly fetches and processes the content from the URL you provide. This is a straightforward way to analyze the live content of a single page.
  2. URL Context with Google Search Grounding: In this mode, the tool can read content from a URL and then use Google Search for a more in-depth understanding.

What makes Ivan’s script so elegant is how it combines this tool with the simplicity of Google Sheets. For each row, the script constructs a prompt for the Gemini API, asking it to analyse the content of the provided URL and find the specific information defined in the spreadsheet headers.

The result is a system where modifying your data query is as easy as renaming a column. This turns a potentially complex scraping task into a simple spreadsheet edit, allowing anyone to create powerful, custom data importers.

To get the full details and the code to build this yourself, check out Ivan’s original post.

Source: Vibe Scraping with Google Apps Script and Gemini’s URL Context ~ kutil.org

‘AI Agents’ in Google Apps Script: Automate Google Workspace with Natural Language

Imagine that you write in plain English what you want to do in Google Workspace (eg. workflows) and it happens just like magic. Insert text prompt, Gemini will generate the code for you and run it immediately. A dream? No, reality, thanks to my conceptual and practical idea of how to implement AI Agents in Google Apps Scripts to leverage the V8 runtime.

Ivan Kutil has explored the concept of AI Agents in Google Apps Script, enabling Google Workspace automation via plain English descriptions. Users describe their automation needs in natural language, which is then processed by Gemini API to generate the necessary code. The generated code is then executed in your Google Apps Script project.

Ivan’s solution uses the gemini-2.0-flash-thinking-exp-01-21 model, an experimental model within Vertex AI specifically designed to reveal its ‘thinking process’, resulting in more reliable code generation. The enhanced reasoning capabilities of this model are particularly beneficial for complex automation tasks, making it a powerful tool for Google Workspace customisation.

To ensure that the agent is doing the right thing, the clever bit is you can test the execution via a dry-run, where the code created with Gemini Flash Thinking is sent to an internal ‘Tester’ agent, which uses Gemini to comment on the code and summarises it in a log. It’s important to review the script before running it, as Ivan accepts no responsibility for the results of the script. Another nice feature is the generated code is stored in the Cache, so after running a dry-run and then a run, the same version will be executed within the Cache limit (currently set to 5 minutes).

This solution, which mirrors Gemini for Workspace’s ability to generate and execute basic Python code, suggests a future where Gemini for Workspace could write and execute Apps Script code for basic tasks. This has the potential to transform how users interact with and automate their Google Workspace environments.

If you are interested in a version of Ivan’s solution that incorporates my GeminiApp library, follow this link. For additional information on Ivan’s solution including setup instructions follow the source link.

Source: Create AI agents in Google Apps Script with Vertex AI and Gemini

Smart replacing images in Google Slides with Gemini Pro API and Vertex AI

Image credit: Ivan Kutil

Surely, you have also experienced having a presentation in which you needed to replace old content with new. Replacing text is very simple because you just need to use the Replace function, and you can either do it in the Google Slides user interface.

The problem arises when you need to replace one image with another, for example, if your corporate logo is updated to a new graphic design or if one of your favorite cloud services updates its icons (Gmail, blink blink ;-) It’s still somewhat bearable with one presentation, but what do you do when, like me, you have thousands of Google Slides files on your Google Drive?

This post explores a clever application of the Gemini API’s multimodal capabilities, created by Ivan Kutil. His code utilizes GenAI to automatically detect outdated logos within your Google Slides presentations. The original blog post (March 2024) used the Gemini Pro Vision API. As a sign of how rapidly this area evolves, Google now recommends switching to Gemini 1.5 Flash or Gemini 1.5 Pro.

Switching to the newer APIs is very straightforward as all you need to do is search the source code for models/gemini-pro-vision and replace with models/gemini-1.5-flash or models/gemini-1.5-pro.

For Apps Script projects I lean towards Gemini 1.5 Flash as it is designed for speed. I’m also always looking to opportunities to test the GeminiApp library for Apps Script and very quickly I was able to fork Ivan’s code and use Gemini 1.5 Flash using a service account.

While GeminiApp requires some initial setup, it offers significant advantages:

  • Easy Model Testing: Experiment with various models quickly.
  • Built-in Features: Includes functionalities like exponential backoff.

If you would like to explore here is a sample slide deck you can copy which has the container bound forked code (if you don’t want to use a service account here is more information on other setup options).

Source: Smart replacing images in Google Slides with Gemini Pro API and Vertex AI

16 secrets tips, tricks and features for new Google Apps Script Editor (v2020)

Google Apps Script has a new editor, which is better, nicer, and completed ready for future new features. Today, I would like to introduce you to several dirty and secret tricks, what you can do.

If you like a keyboard shortcut you’ll love this post from Ivan Kutil! For those less keen on keyboard commands there is still plenty of other productivity tips from Ivan shared in this post.

Source: 16 secrets tips, tricks and features for new Google Apps Script Editor (v2020)

Machine learning in Google Sheet with Tensorflow.js and Google Apps Script 

This article will show you how you can setup, train, and predict spreadsheet data with deep-learning framework Tensorflow.js. You don’t need to call REST APIs or use other 3rd parties storage and algorithm. All your data stays in your secure Google Sheet.

Source: kutil.org: Machine learning in Google Sheet with Tensorflow.js and Google Apps Script

How to measure latency between Google Apps Script project and Google Cloud Platform regions ~ kutil.org

How to measure latency between Google Apps Script project (with UrlFetchApp) and Google Cloud Platform regions (e.g. if you are choosing region for GCP project)

Source: How to measure latency between Google Apps Script project and Google Cloud Platform regions ~ kutil.org

Deploy Google Apps Script web app as an Android application ~ kutil.org

Step-by-step tutorial how to deploy Google Apps Script to Google Play Store for internal web apps usage

Source: Deploy Google Apps Script web app as an Android application ~ kutil.org