如何通过Apps Script批量向Google Sheet插入图片Blob以提升效率

Hey there! I totally get the pain of waiting 25-30 minutes to insert 999 images one by one—those slow loops in Apps Script can be brutal thanks to the overhead of individual sheet interactions. Let's go over two solid approaches to fix this, with the first being the game-changer for speed.

Option 1: Use the Google Sheets API for Batch Operations #

The native insertImage() method is single-image only, but the Sheets API lets you send batch requests to insert dozens/hundreds of images in one go. This cuts down on the back-and-forth between your script and Google's servers, which is the main cause of your slowdown.

Steps to Implement: #

1. Enable the Sheets API: In your Apps Script editor, go to Extensions > Apps Script, then click Services > + Add Service, find Google Sheets API, and enable it.
2. Use this script:

function batchInsertImages() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getActiveSheet();
  const sheetId = sheet.getSheetId();
  
  // Get image URLs and filter out empty cells
  const imageUrls = sheet.getRange('B2:B1000')
    .getValues()
    .map(row => row[0])
    .filter(url => url.trim() !== '');

  // Fetch all images and convert blobs to base64 (required for Sheets API)
  const responses = URLFetchApp.fetchAll(imageUrls);
  const base64Images = responses.map(response => {
    const blob = response.getBlob();
    return Utilities.base64Encode(blob.getBytes());
  });

  // Build batch requests for each image
  const requests = base64Images.map((base64, index) => {
    // Sheets API uses 0-indexed rows/columns: A2 = row 1, column 0
    return {
      insertImage: {
        imageContent: base64,
        destination: {
          sheetId: sheetId,
          rowIndex: index + 1,
          columnIndex: 0
        },
        // Set your desired image size
        width: 50,
        height: 50
      }
    };
  });

  // Execute the batch update
  if (requests.length > 0) {
    Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheet.getId());
  }
}

Why this works: #

Instead of sending 999 separate requests to insert images, you send one request with 999 instructions. This should cut your execution time down to minutes instead of half an hour.

Option 2: Optimize the Existing Loop (No API Required) #

If you'd rather avoid enabling the Sheets API, you can tweak your loop to reduce overhead. It won't be as fast as the batch API, but it'll still be better than your original code.

function optimizedLoopInsert() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const imageUrls = sheet.getRange('B2:B1000')
    .getValues()
    .map(row => row[0])
    .filter(url => url.trim() !== '');

  // Pause spreadsheet rendering to speed up inserts
  SpreadsheetApp.flush();
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.setSpreadsheetLocale('en'); // Reduces rendering overhead in some cases
  sheet.setFrozenRows(0); // Unfreeze rows temporarily

  const responses = URLFetchApp.fetchAll(imageUrls);

  responses.forEach((response, index) => {
    try {
      const imageBlob = response.getBlob();
      // Insert into column A, row index + 2 (matches B2:B1000)
      sheet.insertImage(imageBlob, 1, index + 2)
        .setHeight(50)
        .setWidth(50);

      // Flush updates every 50 images to avoid memory buildup
      if ((index + 1) % 50 === 0) {
        SpreadsheetApp.flush();
      }
    } catch (error) {
      console.log(`Failed to insert image at row ${index + 2}: ${error.message}`);
    }
  });

  SpreadsheetApp.flush();
  // Restore frozen rows if you had them
  // sheet.setFrozenRows(1);
}

Key optimizations here: #

• Filter out empty URLs to skip unnecessary work
• Pause spreadsheet rendering during inserts
• Flush updates in batches instead of after every image
• Add error handling to avoid stopping the entire process if one image fails

Important Notes: #

• Google Sheets doesn't actually let you embed images as "cell content" (like text or numbers) without using the IMAGE() formula. The images inserted via these methods are floating objects positioned over cells—you can resize them to fit perfectly in the cell, which gives the appearance of being part of the cell.
• If you have more than 1000 images, split your URL list into batches of 1000 (both fetchAll and Sheets API batch requests have limits here).
• Ensure all image URLs are publicly accessible or that your script has permission to fetch them (e.g., no auth-walled URLs unless you handle authentication).

内容的提问来源于stack exchange，提问作者Aamir Khan Maarofi