如何在Google Sheets中通过URL获取远程图片的宽高

Absolutely! You can totally grab the width and height of those remote images in Google Sheets. Since there’s no built-in formula for this out of the box, the most reliable way is to use a custom Apps Script function that fetches the image’s metadata. Let’s break down the methods:

Method 1: Custom Apps Script Function (Most Reliable) #

This is the go-to approach because it directly requests the image file and reads its actual dimensions, no workarounds needed.

Step 1: Open the Script Editor #

• In your Google Sheet, click Extensions > Apps Script from the top menu bar.
• Clear the default code in Code.gs, then paste in this script:

function GET_IMAGE_DIMENSIONS(imageUrl) {
  if (!imageUrl) return "No URL provided";
  
  try {
    // Fetch the image and convert it to a blob
    const response = UrlFetchApp.fetch(imageUrl, {muteHttpExceptions: true});
    if (response.getResponseCode() !== 200) return "Failed to load image";
    
    const blob = response.getBlob();
    const contentType = blob.getContentType();
    
    // Make sure we're dealing with an image file
    if (!contentType.startsWith('image/')) return "Not an image file";
    
    // Use Google's Image service to extract dimensions
    const image = ImagesApp.createImage(blob);
    return [image.getWidth(), image.getHeight()];
    
  } catch (error) {
    return `Error: ${error.message}`;
  }
}

Step 2: Use the Custom Function in Your Sheet #

Go back to your spreadsheet. Let’s say your image URLs are in column A (starting at A2):

• To get both width and height in one cell (as an array), enter this in B2:

  =GET_IMAGE_DIMENSIONS(A2)
  

• To split them into separate cells:
  • Width in B2: =INDEX(GET_IMAGE_DIMENSIONS(A2), 1)
  • Height in C2: =INDEX(GET_IMAGE_DIMENSIONS(A2), 2)

Important Notes #

• The first time you run the function, you’ll need to authorize it (this is safe—Google just needs permission to fetch the image URLs you provide).
• Some image servers block requests from external services (like Google’s Apps Script servers) due to hotlink protection. If you get an error, try downloading the image and rehosting it (e.g., on Google Drive) then use that URL instead.
• Apps Script has daily request quotas, so if you’re processing hundreds of images, split the work into batches to avoid hitting limits.

Method 2: Workaround with IMAGE Function (Limited Use) #

If you want to avoid scripts, there’s a janky workaround—but it’s not ideal for most cases:

1. Insert the image into a cell using =IMAGE(A2) (say, in cell C2).
2. Resize the cell to fit the image perfectly (use Format > Row > Fit to data and Format > Column > Fit to data).
3. Use the CELL function to get the cell’s dimensions:
   • Cell width (approximates image width): =CELL("width", C2)
   • Cell height (approximates image height): =CELL("height", C2)

Why This Isn’t Great #

• The cell dimensions won’t match the image’s original size if Google Sheets auto-scales the image (which it does for large images).
• It’s manual and not feasible for bulk processing.
• Results are approximate at best.

Stick with the Apps Script method for accurate, scalable results.

内容的提问来源于stack exchange，提问作者Pascal Chinchilla