Abstract

This report explores using the Gemini Pro API with Google Apps Script to achieve flexible data categorization.

Introduction

The recent release of the LLM model Gemini as an API on Vertex AI and Google AI Studio opens a world of possibilities. Ref and Ref I believe Gemini API significantly expands the potential of Google Apps Script and paves the way for diverse applications. In this report, I present the flexible categorization of data using Gemini Pro API with Google Apps Script.

This report uses Google Apps Script. But, this method can be also used for other languages.

Usage

In order to test this script, please do the following flow.

1. Create an API key

Please access https://makersuite.google.com/app/apikey and create your API key. At that time, please enable Generative Language API at the API console. This API key is used for this sample script.

This official document can be also seen. Ref.

2. Create a Google Apps Script project

Please create a standalone Google Apps Script project. Of course, this script can be also used with the container-bound script.

And, please open the script editor of the Google Apps Script project.

3. Sample script 1

Recently, “Function calling” has been implemented to “Method: models.generateContent” of v1beta. Ref When I read the official document, I thought that this might be able to be used for categorizing texts. So, I created the following sample script.

Please copy and paste the following script to the script editor, set your API key to the function getCategory1_, and save the script.

In this sample script, please run a function sample1.

/**
 * ### Description
 * Get a category from a search text and give categories using the function calling of "Method: models.generateContent".
 *
 * @param {String} searchText Search text.
 * @param {Array} categories 1-dimensional array including categories.
 * @return {String} Return a selected category related to the search text from the categories.
 */
function getCategory1_(searchText, categories) {
  const apiKey = "###"; // Please set your API key.

  const function_declarations = categories.map(({ category, description }) => ({
    name: category,
    description: description,
  }));
  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-pro:generateContent?key=${apiKey}`;
  const q = `Read the following text and select the related category.`;
  const payload = {
    contents: [{ parts: [{ text: q }, { text: searchText }] }],
    tools: [{ function_declarations }],
  };
  const options = {
    payload: JSON.stringify(payload),
    contentType: "application/json",
  };
  const res = UrlFetchApp.fetch(url, options);
  const obj = JSON.parse(res.getContentText());
  if (
    obj.candidates.length > 0 &&
    obj.candidates[0].content.parts.length > 0 &&
    obj.candidates[0].content.parts[0].functionCall
  ) {
    console.log(obj.candidates[0].content.parts[0].functionCall.name);
    if (
      !categories.some(
        ({ category }) =>
          category == obj.candidates[0].content.parts[0].functionCall.name
      )
    ) {
      return "other";
    }
    return obj.candidates[0].content.parts[0].functionCall.name;
  }
  return "No response.";
}

// Please run this function.
function sample1() {
  // Please set a text you want to categorize.
  const searchTexts = ["penguin", "sushi", "orange", "spinach", "toyota lexus"];

  // Please set your categories including the descriptions.
  const categories = [
    {
      category: "fruit",
      description:
        "Nature's candy! Seeds' sweet ride to spread, bursting with colors, sugars, and vitamins. Fuel for us, future for plants. Deliciously vital!",
    },
    {
      category: "vegetable",
      description:
        "Not just leaves! Veggies sprout from roots, stems, flowers, and even bulbs. Packed with vitamins, minerals, and fiber galore, they fuel our bodies and keep us wanting more.",
    },
    {
      category: "vehicle",
      description:
        "Metal chariots or whirring steeds, gliding on land, skimming seas, piercing clouds. Carrying souls near and far, vehicles weave paths for dreams and scars.",
    },
    {
      category: "human",
      description:
        "Walking contradictions, minds aflame, built for laughter, prone to shame. Woven from stardust, shaped by clay, seeking answers, paving the way.",
    },
    {
      category: "animal",
      description:
        "Sentient dance beneath the sun, from buzzing flies to whales that run. Flesh and feather, scale and claw, weaving instincts in nature's law. ",
    },
    {
      category: "other",
      description: "Except for fruit, vegetable, vehicle, human, and animal",
    },
  ];

  const res = searchTexts.map((searchText) => ({
    searchText: searchText,
    category: getCategory1_(searchText, categories),
  }));
  console.log(res);
}

When this script is run, the following result is obtained.

[
  { "searchText": "penguin", "category": "animal" },
  { "searchText": "sushi", "category": "other" },
  { "searchText": "orange", "category": "fruit" },
  { "searchText": "spinach", "category": "vegetable" },
  { "searchText": "toyota lexus", "category": "vehicle" }
]

When I tested this script, the correct categories were returned for all texts except for sushi. In most cases, other is returned as the category of sushi. But, there was a case that fruit was returned.

4. Sample script 2

Recently, I published “Semantic Search using Gemini Pro API with Google Apps Script”. When this method is used, the sample script is as follows.

Please copy and paste the following script to the script editor, set your API key to the function getCategory1_, and save the script.

In this sample script, please run a function sample2.

/**
 * ### Description
 * Calculate cosine similarity from 2 arrays.
 *
 * ### Sample script
 * ```
 * const array1 = [1, 2, 3, 4, 5];
 * const array2 = [3, 4, 5, 6, 7];
 * const res2 = UtlApp.cosineSimilarity(array1, array2); // false
 * ```
 *
 * @param {Array} array1 1-dimensional array.
 * @param {Array} array2 1-dimensional array.
 * @return {Number} Calculated result of cosine similarity.
 */
function cosineSimilarity_(array1, array2) {
  const dotProduct = array1.reduce((t, e, i) => (t += e * array2[i]), 0);
  const magnitudes = [array1, array2]
    .map((e) => Math.sqrt(e.reduce((t, f) => (t += f * f), 0)))
    .reduce((t, f) => (t *= f), 1);
  return dotProduct / magnitudes;
}

/**
 * ### Description
 * Request "Method: models.embedContent" of Gemini Pro API.
 *
 * @param {Object} requests Request body for Method: models.batchEmbedContents.
 * @return {Number} Embedding generated from the input texts.
 */
function getEmbedding_(requests) {
  const apiKey = "###"; // Please set your API key.

  const url = `https://generativelanguage.googleapis.com/v1/models/embedding-001:batchEmbedContents?key=${apiKey}`;
  const options = {
    payload: JSON.stringify({ requests }),
    contentType: "application/json",
  };
  const res = UrlFetchApp.fetch(url, options);
  return (obj = JSON.parse(res.getContentText()));
}

/**
 * ### Description
 * Get category from a search text and giving categories using the semantic search.
 *
 * @param {Array} searchTexts 1-dimensional array including the search texts.
 * @param {Array} categories 1-dimensional array including categories.
 * @return {String} Return a selected category related to the search text from the categories.
 */
function getCategory2_(searchTexts, categories) {
  const r1 = searchTexts.map((searchText) => ({
    model: "models/embedding-001",
    content: { parts: [{ text: searchText }] },
    taskType: "RETRIEVAL_QUERY",
  }));
  const r2 = categories.map(({ category, description }) => ({
    model: "models/embedding-001",
    content: { parts: [{ text: category }, { text: description }] },
    taskType: "RETRIEVAL_DOCUMENT",
    title: category,
  }));
  const obj = getEmbedding_([...r1, ...r2]);
  const embeddingsAr = obj.embeddings.map((e) => e.values);
  const embeddingsR1 = embeddingsAr.splice(0, r1.length);
  const embeddingsR2 = embeddingsAr;
  return embeddingsR1.map((e, i) => {
    const temp = embeddingsR2
      .map((ar, j) => ({
        cs: cosineSimilarity_(ar, e),
        searchText: searchTexts[i],
        category: categories[j].category,
      }))
      .sort((a, b) => (a.cs < b.cs ? 1 : -1));
    return { searchText: temp[0].searchText, category: temp[0].category };
  });
}

// Please run this function.
function sample2() {
  // Please set a text you want to categorize.
  const searchTexts = ["penguin", "sushi", "orange", "spinach", "toyota lexus"];

  // Please set your categories including the descriptions.
  const categories = [
    {
      category: "fruit",
      description:
        "Nature's candy! Seeds' sweet ride to spread, bursting with colors, sugars, and vitamins. Fuel for us, future for plants. Deliciously vital!",
    },
    {
      category: "vegetable",
      description:
        "Not just leaves! Veggies sprout from roots, stems, flowers, and even bulbs. Packed with vitamins, minerals, and fiber galore, they fuel our bodies and keep us wanting more.",
    },
    {
      category: "vehicle",
      description:
        "Metal chariots or whirring steeds, gliding on land, skimming seas, piercing clouds. Carrying souls near and far, vehicles weave paths for dreams and scars.",
    },
    {
      category: "human",
      description:
        "Walking contradictions, minds aflame, built for laughter, prone to shame. Woven from stardust, shaped by clay, seeking answers, paving the way.",
    },
    {
      category: "animal",
      description:
        "Sentient dance beneath the sun, from buzzing flies to whales that run. Flesh and feather, scale and claw, weaving instincts in nature's law. ",
    },
    {
      category: "other",
      description: "Except for fruit, vegetable, vehicle, human, and animal",
    },
  ];

  const res = getCategory2_(searchTexts, categories);
  console.log(res);
}

When this script is run, the following result is obtained.

[
  { "searchText": "penguin", "category": "animal" },
  { "searchText": "sushi", "category": "other" },
  { "searchText": "orange", "category": "other" },
  { "searchText": "spinach", "category": "vegetable" },
  { "searchText": "toyota lexus", "category": "other" }
]

It was found that only penguin and spinach return the correct categories. From this result, when I modify the value of searchTexts as follows,

const searchTexts = [
  "Flightless birds, tuxedoed swimmers, diving pros – they waddle on land, but 'fly' underwater in the Southern Hemisphere!",
  "bite-sized vinegared rice topped with raw seafood or other savory ingredients, often dipped in soy sauce and enjoyed with wasabi and pickled ginger.",
  "Tangy citrus fruit, orange boasts vibrant color and sweetness, packed with vitamin C, enjoyed fresh, juiced, or baked in delectable treats.",
  "Popeye's power-up! Leafy green packed with vitamins and iron, good for eyes, muscles, and even Popeye's biceps ",
  "Toyota's luxury brand, Lexus offers premium cars and SUVs known for comfort, technology, and hybrid options.",
];

the following result is obtained.

[
  {
    "searchText": "Flightless birds, tuxedoed swimmers, diving pros – they waddle on land, but 'fly' underwater in the Southern Hemisphere!",
    "category": "animal"
  },
  {
    "searchText": "bite-sized vinegared rice topped with raw seafood or other savory ingredients, often dipped in soy sauce and enjoyed with wasabi and pickled ginger.",
    "category": "other"
  },
  {
    "searchText": "Tangy citrus fruit, orange boasts vibrant color and sweetness, packed with vitamin C, enjoyed fresh, juiced, or baked in delectable treats.",
    "category": "fruit"
  },
  {
    "searchText": "Popeye's power-up! Leafy green packed with vitamins and iron, good for eyes, muscles, and even Popeye's biceps ",
    "category": "vegetable"
  },
  {
    "searchText": "Toyota's luxury brand, Lexus offers premium cars and SUVs known for comfort, technology, and hybrid options.",
    "category": "vehicle"
  }
]

It is found that the search texts depend on the response values.

Summary

This report introduced the categorization using Gemini Pro API with Google Apps Script.

When “Function calling” of “Method: models.generateContent” is used, even when the simple search texts are used, the correct categorization could be achieved.
When the semantic search with “Method: models.batchEmbedContents” is used, when the simple search texts are used, incorrect categorization occurs. When the explanations of the simple search texts are used, the correct categorization can be achieved.
In the case of “Function calling” of “Method: models.generateContent”, the process cost is higher than that of the semantic search with “Method: models.batchEmbedContents”.

I believe that the process cost will be resolved in the future update.

Note

If in the case that the correct categorization was small, modifying the description might be resolved.
In the current stage, it seems that when image data is used to getCategory1_, an error like Function calling is not enabled for models/gemini-pro-vision occurs. So, in this report, I couldn’t implement the categorization of images yet. But, I believe that this will be resolved in the future update. If you want to implement the categorization of images, a current workaround is as follows. 1. Retrieve the explanation of the image as a text. 2. Categorize the image using the retrieved text using the above script.

Categorization using Gemini Pro API with Google Apps Script