Replace text in a Google Sheet with an image

The following Google Apps Script is designed to replace text in a Google Sheet. You provide the text you want to search for and it will replace all instances of that text (specifically the contents of that cell) with your image. You can optionally choose to adjust the size of the row/column and centre your image. You will need to ensure that whatever image you use, it is accessible online via a URL.

Note this is one of about four different ways of inserting an image into a Google Sheet.

I have a similar blog post available for replacing text in a Google Doc with an image.

Screenshot of Google logo that has replaced text in a Google Sheet

Instructions

In this example the code is designed to sit behind the Google Sheet so it is bound to it. There are 4 pieces of information to complete in order to setup the script:

1. searchText - this is the unique string/pattern in the Sheet that you want to replace with an image.
   
   
2. imageURL - this is the direct link to the image in Google Drive or online that you wish to use in the Sheet.
   
   
3. rowHeight - a numerical value representing the number of pixels for the row height where the image has been inserted.
   
   
4. columnWidth - a numerical value representing the number of pixels for the column width where the image has been inserted.

The Code

Getting/creating the image suitable for the cell is a big requirement for this process, and is achieved via 'CellImageBuilder'. Here we specify the image source URL, alongside additional options such as alt text for accessibility:

// build image

var image = SpreadsheetApp

  .newCellImage()

  .setSourceUrl(imageURL)

  .build();

After iterating through the Google Sheet we have optional values such as cell size and alignment we can tweak:

dataSheet.setRowHeight(row, rowHeight);

dataSheet.setColumnWidth(col, columnWidth);

cell.setVerticalAlignment("middle");

cell.setHorizontalAlignment("center");

Download

Replace text in a Google Sheet with an image download (please use 'File' > 'Make a copy' for your own version).