DEV Community: Boris Baublys

Extracting Images from Google Sheets with Sheets Image Exporter

Boris Baublys — Fri, 30 May 2025 21:22:53 +0000

Original here: How to Export Images from Google Sheets with Sheets Image Exporter

Google Sheets is a powerful tool for managing data, but unlike Microsoft Excel, it lacks a native feature for exporting images embedded in spreadsheets. Whether you’re working with product photos, visual assets, or other images, there’s no built-in way to extract them without manual effort, which can be time-consuming. The Sheets Image Exporter Chrome extension fills this gap, enabling users to export images from Google Sheets efficiently. This article explores how Sheets Image Exporter works, its practical applications, and key considerations for its use.

What Sheets Image Exporter Does

Sheets Image Exporter is a Chrome extension designed to extract images embedded in Google Sheets, a functionality not available natively in the platform. It enables the export of all images from a spreadsheet in a single operation, saving them as PNG or JPEG files. Users can choose to store the images on Google Drive or download them directly to their device. Additionally, file names can be customized based on cell values or sequential numbering for better organization.

The Core Innovation: Image Detection Mechanism

One of the biggest challenges in developing the Sheets Image Exporter was reliably identifying images embedded in Google Sheets, particularly distinguishing between selected and unselected images. Initially, I explored using the Google Sheets API, hoping to leverage methods like getUrl() from the OverGridImage class to retrieve image data. However, this approach quickly proved problematic.

The Problem with Google Sheets API

The Google Sheets API, while powerful for many tasks, has significant limitations when it comes to handling images. The OverGridImage class, which represents images floating over the grid in a spreadsheet, does not provide a robust way to differentiate one image from another or to determine whether an image is selected by the user. The getUrl() method, which was intended to retrieve the URL of an image, was inconsistent even in its early days. More recently, Google deprecated this method entirely, rendering it unusable.

As documented in the Google Apps Script Reference for OverGridImage, the getUrl() method is marked as deprecated with the following note:

Deprecated. For most newly inserted images, the source URL is unavailable.

This deprecation left a gap in the ability to programmatically access image data directly through the API, forcing a rethink of the approach.

A DOM-Based Solution

Faced with these limitations, I turned to the browser’s Document Object Model (DOM) for a solution. The idea was to bypass the Google Sheets API entirely and instead identify images directly in the webpage’s DOM structure. By inspecting the Google Sheets interface, I conducted experiments that revealed images embedded in the spreadsheet are represented as <img> elements with src attributes starting with blob:https://docs.google.com/ or filesystem:https://docs.google.com/. This discovery was pivotal, as it allowed the extension to detect images by querying the DOM rather than relying on the unreliable API.

To make this approach user-friendly and flexible, I implemented a CSS selector-based mechanism to locate images. The extension uses a default selector to identify images that the user has selected (highlighted) with the mouse, leveraging a specific CSS property in the Google Sheets interface. Specifically, selected images are contained within elements that have a style attribute including cursor: -webkit-grab. This led to the default selector defined in popupImages.js:

const defaultSelector = 'div.waffle-borderless-embedded-object-overlay[style*="cursor: -webkit-grab"]';

This selector targets <div> elements with the class waffle-borderless-embedded-object-overlay that have the cursor: -webkit-grab style, which corresponds to images actively selected by the user in the Google Sheets interface.

Flexibility with Custom Selectors

Recognizing that users might want to customize how images are detected, I added the ability to input a custom CSS selector through the extension’s popup interface. This allows advanced users to target specific elements in the DOM if needed. For example, to retrieve all images (including unselected ones), a user could use a selector like div.waffle-borderless-embedded-object-overlay[style*="cursor: default"], which targets images that are not actively selected.

The logic for handling selectors, including falling back to the default if a custom selector is invalid, is implemented in content.js:

try {
    selectedImageContainers = document.querySelectorAll(selector);
    console.log(`Found ${selectedImageContainers.length} containers with selector: ${selector}`);
} catch (e) {
    console.warn(`Invalid selector: ${selector}, falling back to default: ${defaultSelector}`);
    selector = defaultSelector;
    selectedImageContainers = document.querySelectorAll(defaultSelector);
    console.log(`Found ${selectedImageContainers.length} containers with default selector`);
}

This code ensures that if a user-provided selector is invalid, the extension gracefully reverts to the default selector, maintaining functionality while providing feedback via the console.

Processing Images in the DOM

Once the images are identified, the extension processes them by fetching their blob URLs and converting them to data URLs for further use (e.g., saving to Google Drive or downloading locally). This is handled in content.js with the following logic:

const img = container.querySelector('img[src^="blob:https://docs.google.com/"], img[src^="filesystem:https://docs.google.com/"]');
if (img) {
    processImage(img, index, () => {
        completed++;
        if (completed === selectedImageContainers.length) {
            const filteredDataUrls = dataUrls.filter(url => url !== null);
            console.log(`Sending ${filteredDataUrls.length} unique images.`);
            sendResponse({ images: filteredDataUrls, selectorUsed: selector });
        }
    });
}

This snippet demonstrates how the extension identifies <img> elements with blob or filesystem URLs, processes them into data URLs, and sends the results back to the popup script for display or export.

Why This Matters

The DOM-based image detection mechanism is the heart of the Sheets Image Exporter. By moving away from the Google Sheets API and leveraging the browser’s DOM, the extension overcomes the limitations of the deprecated getUrl() method and provides a reliable way to identify and extract images. The default selector ensures ease of use for most users, while the custom selector option adds flexibility for advanced scenarios. This approach not only solves the technical challenge but also enhances the user experience by making image extraction intuitive and robust.

Practical Applications

The extension is useful across various scenarios:

E-commerce: Extract product images from inventory spreadsheets for use on websites or marketplaces.
Education: Retrieve visual assets from teaching materials or student projects for reports or digital portfolios.
Project Management: Export images, such as logos or screenshots, from project tracking sheets for inclusion in reports or presentations.
Marketing: Access campaign visuals, like social media graphics or banners, directly from a spreadsheet.

These use cases illustrate how the extension addresses a critical need for users managing visual data in Google Sheets.

Key Features

Sheets Image Exporter provides several practical features:

Batch Export: Extracts all images from a spreadsheet in one operation, addressing the absence of native export functionality.
Output Formats: Saves images as PNG or JPEG, based on user preference.
File Naming Options: Allows naming files using cell values or sequential numbers for organized output.
Google Drive Integration: Enables saving images directly to Google Drive, keeping them accessible within the Google ecosystem.
Local downloading: Download of all selected images to your device in a single ZIP archive.

The extension operates through the Chrome browser and has a straightforward interface, requiring minimal setup.

Considerations for Use

When using Sheets Image Exporter, a few factors should be kept in mind:

Image Quality: The extension preserves the original resolution of images, so the quality of exported files depends on the quality of images uploaded to the spreadsheet.
Google API Limits: For spreadsheets with many images, exporting may need to be done in batches to stay within Google’s API quotas.
Image Types: The extension extracts images placed over cells but does not support exporting charts or drawings created within Google Sheets.

Conclusion

Sheets Image Exporter provides a critical capability that Google Sheets lacks: the ability to export images embedded in spreadsheets, a feature readily available in Microsoft Excel. For users managing visual data—whether for business, education, or personal projects—this Chrome extension offers a practical solution to a significant limitation. It is available through the Chrome Web Store and can be integrated into any Google Sheets workflow accessed via Chrome.

Telegram bot + GoogleSheets: solving some problems

Boris Baublys — Sat, 07 May 2022 23:39:21 +0000

What problems you may encounter and how to get around them

The topic of Telegram bots is only growing. If you're developing Google Sheets add-ons, you're writing Google App Script (GAS) code. Therefore, probably, the brains for these bots are written in GAS.
There was an idea to write a bot that would look for keywords in chat messages and do something with these messages. For example, send me a notification that someone has written something on a topic that interests me. Or, on the contrary, delete obscene language and spam, and ban the authors of such messages in the chat.
As always, in the process of practical implementation of the plan, pitfalls emerge. I will talk about some of them and ways to bypass them below. But first, some theory.

getUpdates VS setWebhook?

It doesn't matter what language you write the brains for your bot in - GAS, PHP, Pyton or something else. In any case, you are using the Telegram API.
The API currently supports two ways to handle bot updates: getUpdates and setWebhook.
getUpdates is a pull mechanism, setWebhook is a push mechanism.
For example, you run getUpdates and get all the messages written in the chat during that hour. In GAS, you can set up a time trigger with this interval. But this means that for our example with an anti-spam bot, spam will hang in the chat with impunity for an hour. This is not good, it is advisable to shoot spam immediately on approach.
Therefore it is necessary to use setWebhook. As stated in Marvin's Wonderful Guide to All Things Webhook:
setWebhook, compared to getUpdates,

saves your bot from having to frequently request updates.
avoids the need for any polling mechanism in your code.

setWebhook() + fetch(url)

Now a few words about the mechanism for receiving updates or, more simply, messages from a Telegram chat.

We write the simplest script in the GAS editor:

function doPost(e) { var contents = JSON.parse(e.postData.contents); var chat_id = contents.message.from.id; // code for extracting data and writing to the sheet }
That's it, we have an update in the contents variable. With the help of further processing, everything that is needed can be extracted from it. For example, chat_id is the identifier of the message sender, text is the text of the message.
Then you can write this data on a sheet, send it to someone, etc.

Let's deploy the script as a web application.
At the same time, we specify the parameters “Run as: On my behalf, Who has access: Everyone” and copy the link to the web application (webAppUrl).
We form a link.
To do this, you need to connect the bot token and the web application URL as follows:
var token = "1234567890:ABCDEFGHIJKLMNOPQRSTUVWXYZ"; var webAppUrl = "https://script.google.com/macros/s/XXXXXXXX/exec"; var telegramUrl = "https://api.telegram.org/bot" + token; var telegramUrl = "https://api.telegram.org/bot" + token;
Fetch the url and set the webhook:
function setWebhook() { var url = telegramUrl + "/setWebhook?url=" + webAppUrl; var response = UrlFetchApp.fetch(url); }

You can go to this url in the browser without any script and see
{"ok":true,"result":true,"description":"Webhook is already set"}

This is a JSON object, parsing which, you can extract all 3 fields separately - ok, result and description. Then they can be used in conditional logic, shown to the user and all that.

Pitfalls

So we have a script that instantly writes all messages from the Telegram chat to a sheet. Then you can search for keywords in them and do something with these messages.
But imagine that you, delighted, have posted a link to this bot of yours in the public domain and expect that satisfied users will thank you for a useful tool.
It wasn't there!

Everyone is comfortable with Google services. And they provide you with sheets, and a script development environment, and powerful servers around the world on which these scripts work, and many other delicious goodies. However there are pitfalls and one of them is Google's quotas and limits. “Apps Script Services has daily quotas and limits on some features. If you exceed the quota or limit, your script will throw an exception and execution will stop.” For example, URL Fetch calls are limited to 20,000 per day, and the limit for simultaneously script executions is 30 users. This means that if the number of users of the bot becomes large enough, it will stop working.
And what to do?
For example, you can buy your own server or rent a server and process all the logic on it. It costs extra money and time, so it's logical to think about something else.

Way out

The developer may come up with the following.
Since the web application is running on behalf of me, my quotas are consumed. But what if you deploy it not on my behalf, but on behalf of the user?
An interested user can do this in 3 clicks. You just need to give him a sheet template with a script embedded in it. He will make a copy of the sheet for himself, deploy the script as a web application and insert the webAppUrl into the desired cell.
Now quotas and restrictions will be consumed not by the developer, but by the user.
And it is very unlikely that this user will have more than 20,000 messages per day in his Telegram group.
Everything seems to work.
It wasn't there!

Pitfalls again

Every developer wants the fruits of his creativity to be useful. Both for him and for the users.
How to distribute our script so that interested users know about it? You can write articles on thematic sites, advertise on Google, etc.
It's all great, but it's a sin not to use free tools.
Speaking of Google goodies, one cannot fail to mention the Google Workspace Marketplace. There, Google developers publish add-ons for Google products - for sheets, documents, mail, etc.
Thus, the idea of implementing our Telegram bot appears as follows:

We make a Google sheet template.
It draws an interface with the necessary headings, a web application script is attached to the table.
We transfer all the message processing logic from the webapp to the add-on and publish it on the Google Workspace Marketplace.
The user installs the add-on, makes a copy of the template for himself, makes the initial settings (enters the bot token, web application URL, his keywords into the required cells) and uses it to his delight.
Yes, here we shifted part of the work to the user, but that's why I am writing this article. Perhaps someone who reads will suggest workarounds.
Moreover, it was not there again, there are still pitfalls here.

Again

The problem is that you can NOT pass data directly into an add-on in ANY WAY. That is, you received a message via the webhook, but you cannot send this message to the handler, which, let me remind you, is in add-on.
You cannot run a function that is inside an add-on.
You can't even pass some tag or signal like “Hey add-on! There's a message! Run fetch(url) and read!”.
Never. I do not know why, whether for security reasons, or for some other reason. You can read about it here: How to transfer data from webapp to addon.

The thought may come to mind: “We write messages on a sheet. Put an onEdit() or onChange() trigger and as soon as the message is written to the sheet, the trigger will fire, read the data and process everything as it should.”
There are 2 catches here.
One of them is that all I/O type spreadsheet operations are relatively slow - it's a fraction of a second. With intensive communication in the Telegram chat, multiple calls to the sheet and from the sheet can cause significant delays.
In contrast to the situation, if the data is passed bypassing the sheets, directly from fetch() to the add-on code. Google's servers are fast and such operations take milliseconds.
The second catch is that onEdit() or onChange() triggers do NOT fire when the data on the sheet is changed programmatically.
These triggers are fired if the data on the worksheet has been changed by the user. For example, entered from the keyboard or selected from the menu.
More about this here: Event Objects.

There is an way!

The answer to the question in the post on the Stack was still found. The author even assigned a bounty for the correct answer, but the best thing that they suggested to him then was to use the Google cloud service. Yes, among the delicious buns there is one. And it's certainly powerful stuff.
That's just it is paid when certain indicators are exceeded.
And I have not seen good manuals for it.
According to GAS there are plenty of such resources. This is Google Apps Script, and mdn web docs and a bunch more.
On Google Cloud there are much fewer such resources.

Therefore, when Alan Wells came to the post six months later and wrote about the Sheets API and USER_ENTERED, everything immediately became clear.

The scheme is as follows:

Enable the Sheets API in web application services. You can verify that everything is correct if you look at the code in the appsscript.json file. If anyone forgot, then for this, in the script settings, check the box “Show the appsscript.json manifest file in the editor”.
It should contain the following code:
"dependencies": { "enabledAdvancedServices": [{ "userSymbol": "Sheets", "serviceId": "sheets", "version": "v4" }] }
Later, when the user does a copy of your template, this service will be included in its copy of the script.
How do we usually write data to a sheet?
We use setValue(value).
Like this:
var ss = SpreadsheetApp.getActiveSpreadsheet(); varsheet = ss.getSheets()[0]; varcell = sheet.getRange("B2"); cell.setValue(100);

Into the fire!

Now we use Google Sheets API.

The code should look something like this:
var SHEETID = 'XXXXXXX'; function doPost(e) { var data = e.postData.contents; var rowValues = [ [data, ""], ]; var request = { 'valueInputOption': 'USER_ENTERED', 'insertDataOption': 'INSERT_ROWS', 'data': [ { "range": "TEMP!A2:B2", "majorDimension": "ROWS", "values": rowValues, }, ], }; var response = Sheets.Spreadsheets.Values.batchUpdate(request, SHEETID); }

There are two key points here:
A. We write to the sheet using batchUpdate().
B. The 'USER_ENTERED' as it were, tells the trigger that the data on the sheet has been changed not by the program, but by the user.
Everything, now the trigger will work. The data, albeit indirectly - through the sheet, is transferred to the add-in, and the whole circuit functions.

Conclusion

You can read more about all this here: Telegram Assistant: Description and manual. There is also a link to the add-on, and to the spreadsheet with the script. You can use all of this freely.
And I will be grateful for constructive criticism and advice!