Power Up with Apps Script & Gemini AI

by

Presentation by Laurence Svekis

Presentation Overview

Laurence Svekis delivered a comprehensive presentation titled “Power Up with Google Apps Script” at the Google IO Extended + Build with AI event. The talk focused on empowering users with the capabilities of Google Apps Script to automate tasks, customize workflows, and integrate various services within the Google Workspace ecosystem. Laurence also demonstrated practical applications of Apps Script and explored how AI tools like Google Gemini can enhance these applications.

Key Sections of the Presentation

Introduction to Google Apps Script

  • What is Google Apps Script?
    • A JavaScript-based scripting language used to automate tasks and extend the functionality of Google Workspace applications.
    • Allows automation of data entry, report generation, email notifications, and more.
  • Key Features:
    • Automation: Simplifies repetitive tasks and workflows.
    • Customization: Enables the creation of custom menus, add-ons, and web applications.
    • Integration: Facilitates integration with Google services and third-party APIs.
    • Accessibility: Cloud-based, making it easy to access, share, and collaborate on projects. It’s also free to use.

Benefits of Using Apps Script

  • Collaboration: Enables team collaboration on automation tasks.
  • User Base: Widely used by individuals, small businesses, educational institutions, nonprofits, and IT professionals.
  • Macro Support: Users can create and record macros, then review and modify the script for more control.

Common Use Cases

  • Building Custom Add-ons: Develop add-ons for Google Sheets and Docs to enhance their functionality for specific use cases.
  • Creating Web Applications: Develop custom web apps that interact with Google services, useful for internal tools and dashboards.

Custom Menu UI in Google Sheets

  • Demonstrated how to create a custom menu in Google Sheets using Apps Script, allowing users to execute functions directly from the menu interface.

Google Sheets Built-in Functions

  • Leverage Built-in Functions: Utilize over 400 built-in functions and create custom functions to extend capabilities.
  • Automate with Macros: Automate solutions with or without coding experience.

Custom Function Example

  • A custom function example was provided that calculates the percentage change between two numbers, showcasing how Apps Script can be used to enhance spreadsheet functionality.

Automation with Triggers

  • Setting Triggers: Explained how to automate scripts based on events, like opening a document or submitting a form.
  • Triggers Without User Intervention: Triggers can be set with code or using Trigger Adders for seamless automation.

One-Liner Code Solutions

  • Presented examples of achieving complex tasks with minimal code, such as translating text with Google Translate using Apps Script.

Integrating AI with Google Apps Script

Build with AI Gemini

  • Google Gemini AI: Introduced the integration of AI tools, specifically Google Gemini, with Apps Script.
  • API Usage: Explained how to obtain an API key for accessing Google Gemini Pro Vision API.

Mini Project: Google Drive Image Processor

  • Objective: Demonstrated a project that lists image files from Google Drive, converts them to Base64, and writes details into Google Sheets.
  • Google Gemini Pro Vision API: Used to generate detailed descriptions for images, showcasing AI’s role in enhancing Apps Script applications.

Key Code Snippets

Project Explanation

  • Constants:
    • IMAGEFOLDER: ID of the Google Drive folder containing images.
    • SHEETID: ID of the Google Sheets document for output.
    • GEMIKEY: API key for the Google Gemini Pro Vision API.
  • listFilesInFolder Function:
    • Retrieves files from the specified folder and appends details to a Google Sheet, including image descriptions generated by the AI.
  • convertToBase64 Function:
    • Converts image files to Base64 strings for processing.
  • createDescription Function:
    • Generates detailed image descriptions using the Google Gemini Pro Vision API.

Example Code

const IMAGEFOLDER = '1TsXWeYPewWxvwcGfgw-WIU216ceGadDe';
const SHEETID = '1v6bK-QcbkDKBo8QniU50_ucpLsBPrSY4MOSqJ0PNtdY';
const GEMIKEY = 'YOUR_API_KEY_HERE'; // Replace with your Gemini Pro Vision API key

const folderId = IMAGEFOLDER;
const folder = DriveApp.getFolderById(folderId);
const files = folder.getFiles();

const sheet = SpreadsheetApp.openById(SHEETID).getSheetByName('list');
sheet.clear();
sheet.appendRow(["File Name", "File URL", "MIME Type", "Description"]);

const imageMimeTypes = [
  "image/jpeg",
  "image/png",
  "image/gif",
  "image/bmp",
  "image/webp",
  "image/svg+xml",
  "image/tiff"
];

while (files.hasNext()) {
  const file = files.next();
  const mimeType = file.getMimeType();
  if (imageMimeTypes.includes(mimeType)) {
    const fileName = file.getName();
    const fileUrl = file.getUrl();
    const base64 = convertToBase64(file);
    const longDescription = createDescription(base64, file.getMimeType());
    sheet.appendRow([fileName, fileUrl, mimeType, longDescription]);
  }
}

function convertToBase64(file) {
  const blob = file.getBlob();
  const base64String = Utilities.base64Encode(blob.getBytes());
  return base64String;
}

const createDescription = (base64, fileMimeType) => {
  try {
    const text = `Provide a detailed long description in less than 255 characters for the image.`;
    const apiUrl = `https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-pro-latest:generateContent?key=${GEMIKEY}`;
    const inlineData = { mimeType: fileMimeType, data: base64 };
    const response = UrlFetchApp.fetch(apiUrl, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      payload: JSON.stringify({ contents: [{ parts: [{ inlineData }, { text }] }] }),
    });
    const data = JSON.parse(response.getContentText());
    return data.candidates[0].content.parts[0].text.trim();
  } catch (f) {
    return null;
  }
};

Feedback and Engagement

  • Student Engagement: Laurence shared insights about his Udemy courses, which have attracted over 250,000 students, emphasizing high engagement levels.
  • Passion for Teaching: Highlighted his enjoyment in teaching Apps Script and using it for personal branding and automation tasks.

Conclusion

  • Empowerment Through Apps Script: The presentation underscored the versatility and power of Google Apps Script as a tool for automating, customizing, and integrating tasks within Google Workspace.
  • Community and Resources: Encouraged attendees to explore further learning resources and engage with the community for continued growth and development.

Resources and Links

This summary encapsulates the key elements of Laurence Svekis’s presentation, highlighting the practical applications of Google Apps Script and its integration with AI technologies.

Laurence Svekis is a distinguished technology educator and developer, with a career spanning over 25 years. With more than a million students enrolled in his online courses, Laurence has become a leading authority in Google Apps Script and JavaScript. His best-selling books on JavaScript has helped countless developers deepen their understanding of the language, solidifying his reputation as an expert in coding and software development.

Throughout his career, Laurence has been at the forefront of innovation in automation and integration, specializing in Google Apps Script to empower users in optimizing their Google Workspace experience. His live presentations and workshops are highly acclaimed, drawing audiences eager to learn about the latest trends in technology and AI integration. As a sought-after speaker, Laurence frequently presents at major technology conferences, where he shares his extensive knowledge and practical insights into streamlining workflows and enhancing productivity through smart coding practices.

Laurence’s passion for teaching and his ability to simplify complex concepts have made him a beloved figure among students and professionals alike. His dedication to fostering learning and development has not only impacted individual learners but also educational institutions and businesses looking to leverage technology for improved efficiency and innovation.