Imagine you’re working on a Google Sheets table and you want to take it up a notch by inserting images. In this article, we’ll explore different methods to add images to cells in Google Sheets, discussing their advantages and limitations.
Using the IMAGE Function
The first method is to use the IMAGE function, which allows you to insert images into any cell in your Google Sheets. It’s easy to do: simply select the desired cell, press F2 to enter formula mode, and then enter the formula =IMAGE("URL"), where URL is the web address of the image you want to insert.
For example, you can use the following formula to add a free image to your Google Sheet:
=IMAGE("https://www.labnol.org/images/2023/200009.png")
Google Sheets will automatically resize the image to fit the selected cell. However, you can modify this behavior by adding another parameter to the IMAGE function.
By using mode 2, as in =IMAGE("URL", 2), the image will stretch to fill the selected cell, occupying the entire height and width. Keep in mind that this may distort the image if the aspect ratio doesn’t match that of the cell.
Alternatively, if you set the mode value to 3, as in =IMAGE("URL", 3), the image will be embedded in the cell while maintaining its original dimensions. If the cell is too small to contain the image, it will be cropped.
Lastly, you can also specify the height and width of the image in pixels by setting the mode to 4. For example, the formula =IMAGE("URL", 4, 100, 100) will embed the image with a size of 100×100 pixels.
Using the Insert Menu in Google Sheets
Another method is to use the Insert menu in Google Sheets to add images directly from your computer. Simply click on “Insert” and then select “Image” to choose and upload the image you want to insert.
Unlike the IMAGE function, this approach allows you to place the image anywhere in your spreadsheet. You can easily resize the image by dragging the blue handles and add alternative text for better accessibility.
Another advantage of this method is that you can assign a Google script to the image, which will be executed when someone clicks on it. For example, you can add a button in your spreadsheet and assign a script that instantly downloads the sheet as a PDF to your computer.
Adding Images with Apps Script
If you’re a developer, you can take it a step further and programmatically add images to Google Sheets using the setFormula() method or the CellImageBuilder API in Google Apps Script.
Using the setFormula() method
This script will insert a public image from the web into the first cell (A1) of the active Google Sheets. As we haven’t specified the mode in the IMAGE formula, the image will be resized to fit the cell while maintaining the aspect ratio.
Using the CellImageBuilder API
This is a relatively new feature of Google Apps Script that enables you to add images within a cell. You can specify the image URL, alternative text, and the image will automatically be resized to fit the specified cell.
It’s recommended to use a try-catch block to handle errors if the image URL is invalid or inaccessible.
The CellImage API also allows you to use base64-encoded image strings instead of image URLs. You can use Google Apps Script to convert an image to a base64-encoded string and pass that string to the CellImageBuilder API.
In conclusion, there are various ways to insert images into cells in Google Sheets. Whether you use the IMAGE function, the Insert menu, or Apps Script, you can make your spreadsheet more visually appealing and interactive. For more information on Google Sheets and other tips, visit Crawlan.com.
