Inserting Generated Text to Google Documents, Google Spreadsheets, and Google Slides using Gemini Pro API with Google Apps Script

GasTips / document / spreadsheet / slides
Google Apps Script / Gemini / AI / LLM / document / spreadsheet / slides

Gists

Description

When the generated text can be automatically inserted into the cursor position of Google Document, Google Spreadsheet, and Google Slide, it will be useful for users. This report introduces sample scripts for achieving this.

Sample scripts

Here, I would like to introduce 3 sample scripts for a Google Document, a Google Spreadsheet, and a Google Slide.

Create an API key

These sample scripts request Gemini Pro API using an API key. So, please create your 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.

For Google Document

Please create a new Google Document, open the script editor of Document, copy and paste the following script to the script editor, and save the script.

First, please set your API key.

When you use this script, please reopen your Google Document. By this, a custom menu is created. When you run “Open sidebar” from the custom menu, a sidebar is opened. When you set the cursor to the document body, input a question to the input tag on the sidebar, and click “ok” button, the generated text by Gemini API is inserted into the cursor position. The demonstration is as follows.

Code.gs

const onOpen = (_) =>
  DocumentApp.getUi()
    .createMenu("sample")
    .addItem("Open sidebar", "openSidebar")
    .addToUi();
const openSidebar = (_) =>
  DocumentApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile("index").setTitle(
      "Put a generated text by Gemini API"
    )
  );

function getGeneratedText_(text) {
  const apiKey = "###"; // Please set your API key.

  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-pro:generateContent?key=${apiKey}`;
  const payload = { contents: [{ parts: [{ text }] }] };
  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) {
    const t = obj.candidates[0].content.parts[0].text;
    return t;
  }
  return "No response.";
}

function getGeneratedText(text, check) {
  const cursor = DocumentApp.getActiveDocument().getCursor();
  if (!cursor) {
    return "";
  }
  const r = getGeneratedText_(text);
  if (check) {
    cursor.insertText(r);
  }
  return r;
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
    <link
      rel="stylesheet"
      href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"
    />
  </head>

  <body>
    <div class="sidebar branding-below">
      <h1>
        Put a cursor in the document, input question, and click "ok" button.
      </h1>
      <form>
        <div>
          <label for="text">Input question</label>
          <textarea
            id="text"
            name="text"
            rows="5"
            style="width:100%;"
          ></textarea>
        </div>
        <div>
          <input
            type="checkbox"
            name="checkbox"
            id="checkbox"
            value="on"
            checked
          />
          <label for="checkbox">Put result to the cursor position</label>
        </div>
        <input
          type="button"
          class="action"
          value="ok"
          id="button"
          onclick="main(this.parentNode.text);"
        />
      </form>
      <div class="form-group">
        <label for="result">Response from Gemini API</label>
        <textarea id="result" rows="20" style="width:100%;"></textarea>
      </div>
    </div>
    <script>
      function main(e) {
        if (e.value == "") {
          document.getElementById("result").innerHTML = "";
          return;
        }
        const button = document.getElementById("button");
        button.value = "Processing...";
        google.script.run
          .withSuccessHandler((res) => {
            document.getElementById("result").value = res;
            button.value = "ok";
          })
          .getGeneratedText(
            e.value,
            document.getElementById("checkbox").checked
          );
      }
    </script>
  </body>
</html>

For Google Spreadsheet

Please create a new Google Spreadsheet, open the script editor of Spreadsheet, copy and paste the following script to the script editor, and save the script.

First, please set your API key.

When you use this script, please reopen your Google Spreadsheet. By this, a custom menu is created. When you run “Open sidebar” from the custom menu, a sidebar is opened. When you select one of the cells, input a question to the input tag on the sidebar, and click “ok” button, the generated text by Gemini API is inserted into the selected cell. The demonstration is as follows.

Code.gs

const onOpen = (_) =>
  SpreadsheetApp.getUi()
    .createMenu("sample")
    .addItem("Open sidebar", "openSidebar")
    .addToUi();
const openSidebar = (_) =>
  SpreadsheetApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile("index").setTitle(
      "Put a generated text by Gemini API"
    )
  );

function getGeneratedText_(text) {
  const apiKey = "###"; // Please set your API key.

  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-pro:generateContent?key=${apiKey}`;
  const payload = { contents: [{ parts: [{ text }] }] };
  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) {
    const t = obj.candidates[0].content.parts[0].text;
    return t;
  }
  return "No response.";
}

function getGeneratedText(text, check) {
  const selection = SpreadsheetApp.getSelection();
  if (!selection) {
    return "";
  }
  const r = getGeneratedText_(text);
  if (check) {
    selection.getActiveRange().setValue(r);
  }
  return r;
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
    <link
      rel="stylesheet"
      href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"
    />
  </head>

  <body>
    <div class="sidebar branding-below">
      <h1>
        Select a cell in the sheet, input question, and click "ok" button.
      </h1>
      <form>
        <div>
          <label for="text">Input question</label>
          <textarea
            id="text"
            name="text"
            rows="5"
            style="width:100%;"
          ></textarea>
        </div>
        <div>
          <input
            type="checkbox"
            name="checkbox"
            id="checkbox"
            value="on"
            checked
          />
          <label for="checkbox">Put result to the selected cell.</label>
        </div>
        <input
          type="button"
          class="action"
          value="ok"
          id="button"
          onclick="main(this.parentNode.text);"
        />
      </form>
      <div class="form-group">
        <label for="result">Response from Gemini API</label>
        <textarea id="result" rows="20" style="width:100%;"></textarea>
      </div>
    </div>
    <script>
      function main(e) {
        if (e.value == "") {
          document.getElementById("result").innerHTML = "";
          return;
        }
        const button = document.getElementById("button");
        button.value = "Processing...";
        google.script.run
          .withSuccessHandler((res) => {
            document.getElementById("result").value = res;
            button.value = "ok";
          })
          .getGeneratedText(
            e.value,
            document.getElementById("checkbox").checked
          );
      }
    </script>
  </body>
</html>

For Google Slide

Please create a new Google Slide, open the script editor of Slide, copy and paste the following script to the script editor, and save the script.

First, please set your API key.

When you use this script, please reopen your Google Slide. By this, a custom menu is created. When you run “Open sidebar” from the custom menu, a sidebar is opened. When you select a shape on the slide, input a question to the input tag on the sidebar, and click “ok” button, the generated text by Gemini API is inserted into the selected shape. The demonstration is as follows.

Code.gs

const onOpen = (_) =>
  SlidesApp.getUi()
    .createMenu("sample")
    .addItem("Open sidebar", "openSidebar")
    .addToUi();
const openSidebar = (_) =>
  SlidesApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile("index").setTitle(
      "Put a generated text by Gemini API"
    )
  );

function getGeneratedText_(text) {
  const apiKey = "###"; // Please set your API key.

  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-pro:generateContent?key=${apiKey}`;
  const payload = { contents: [{ parts: [{ text }] }] };
  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) {
    const t = obj.candidates[0].content.parts[0].text;
    return t;
  }
  return "No response.";
}

function getGeneratedText(text, check) {
  const selection = SlidesApp.getActivePresentation().getSelection();
  if (!selection) {
    return "";
  }
  const elements = selection.getPageElementRange().getPageElements();
  if (
    elements.length == 0 &&
    elements[0].getPageElementType() != SlidesApp.PageElementType.SHAPE
  ) {
    return "";
  }
  const r = getGeneratedText_(text);
  if (check) {
    elements[0].asShape().getText().setText(r);
  }
  return r;
}

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
    <link
      rel="stylesheet"
      href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"
    />
  </head>

  <body>
    <div class="sidebar branding-below">
      <h1>
        Select a shape in the slide, input question, and click "ok" button.
      </h1>
      <form>
        <div>
          <label for="text">Input question</label>
          <textarea
            id="text"
            name="text"
            rows="5"
            style="width:100%;"
          ></textarea>
        </div>
        <div>
          <input
            type="checkbox"
            name="checkbox"
            id="checkbox"
            value="on"
            checked
          />
          <label for="checkbox">Put result to the selected shape.</label>
        </div>
        <input
          type="button"
          class="action"
          value="ok"
          id="button"
          onclick="main(this.parentNode.text);"
        />
      </form>
      <div class="form-group">
        <label for="result">Response from Gemini API</label>
        <textarea id="result" rows="20" style="width:100%;"></textarea>
      </div>
    </div>
    <script>
      function main(e) {
        if (e.value == "") {
          document.getElementById("result").innerHTML = "";
          return;
        }
        const button = document.getElementById("button");
        button.value = "Processing...";
        google.script.run
          .withSuccessHandler((res) => {
            document.getElementById("result").value = res;
            button.value = "ok";
          })
          .getGeneratedText(
            e.value,
            document.getElementById("checkbox").checked
          );
      }
    </script>
  </body>
</html>

Note

  • In the current stage, Gemini Pro API can be requested 60 times in 1 minute as the default. Ref Please be careful about this.

Reference

 Share!