Creating a Google Apps Script to List Images in a Folder with Descriptions

We will explore how to create a Google Apps Script that lists all the image files in a specific Google Drive folder, converts them to Base64, and writes their details (name, URL, MIME type, and a generated description) into a Google Sheets spreadsheet. We will use the Google Gemini Pro Vision API to generate detailed descriptions for the images.

Step-by-Step Guide

1. Set Up Your Google Apps Script:
   • Open your Google Sheets document.
   • Click on Extensions > Apps Script.
   • Delete any existing code in the script editor and replace it with the following code snippet:

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

function listFilesInFolder() {
  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();
    Logger.log(mimeType);

    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]);
    }
  }

  Logger.log('File listing complete!');
}

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);
    return data.candidates[0].content.parts[0].text.trim();
  } catch (f) {
    return null;
  }
};

2. Explanation of the Script:
   • Constants:
     • IMAGEFOLDER: The ID of the Google Drive folder containing the images.
     • SHEETID: The ID of the Google Sheets document where the image details will be written.
     • GEMIKEY: Your API key for the Google Gemini Pro Vision API.
   • listFilesInFolder Function:
     • Retrieves the folder using the folder ID.
     • Retrieves all files in the folder.
     • Opens the specified Google Sheets document and clears the existing data.
     • Adds headers to the sheet: “File Name”, “File URL”, “MIME Type”, and “Description”.
     • Iterates through the files, checks if they are images, converts them to Base64, and generates a description using the Google Gemini Pro Vision API.
     • Appends the file details to the sheet.
   • convertToBase64 Function:
     • Converts a file to a Base64 string.
   • getSuggestedFilename Function:
     • Uses the Google Gemini Pro Vision API to generate a detailed description for an image based on its Base64-encoded data and MIME type.
3. Run the Script:
   • Save the script by clicking the floppy disk icon or pressing Ctrl + S.
   • To run the script, click the play button (triangle icon) in the toolbar.
   • Authorize the script to access your Google Drive, Google Sheets, and the external API when prompted.
4. Viewing the Results:
   • Once the script completes, open your Google Sheets document.
   • You will see a list of image files from the specified folder with their names, URLs, MIME types, and generated descriptions.

Conclusion

By following the steps above, you can create a powerful Google Apps Script to list image files from a Google Drive folder, convert them to Base64, and generate detailed descriptions using the Google Gemini Pro Vision API. This script is a great example of how you can leverage Google Apps Script and external APIs to automate tasks and enhance your productivity.