Changing text over image

Question

So the basic idea is to have a picture in Google sheet and have Text on top of it that would change based on variables in the sheet

Example:

So my question is:
Is there a way to do something like this using either cells somehow showing on top of the picture, Or have a drawingwith a text box that you can use logic with so that it can change the value accordingly?

Answer 1

Unfortunately, I think that your goal cannot be directly achieved using the existing methods of Spreadsheet services. So, as a workaround, in your situation, how about using this method? In this method, 2 Google Apps Script libraries are used. Using these libraries, the following flow is run.

1. Retrieve the image size.
2. Create a new Google Slides with a specific size.
3. Put the image on the slides and put the text retrieved from the cell of the Spreadsheet.
4. Retrieve the image and texts as an image blob.
5. Put the image blob to the Spreadsheet. In this case, the existing image is replaced.

By this flow, I thought that your goal might be able to be achieved.

Usage:

1. Install libraries.

Please install 2 Google Apps Script libraries as follows.

1. Install DocsServiceApp. Ref
2. Install ImgApp. Ref

2. Enable Drive API.

Please enable Drive API at Advanced Google services.

3. Sample script.

Before you use this script, please set the fileId, sheet name, and cell range. In this script, the image on Google Drive is used. Because when the image on Spreadsheet is retrieved, the process cost becomes high. So I used this method.

function myFunction() {
  const fileId = "###"; // Please set the file ID of image file.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set the sheet name.
  const value = sheet.getRange("A1").getDisplayValue(); // Please set the range.

  // Please set the text.
  const text = { text: value, left: 10, top: 120, width: 180, height: 60, fontSize: 20 }; // You can manage the inserted text here.

  // 1. Retrieve the image size using ImgApp.
  const file = DriveApp.getFileById(fileId);
  const blob = file.getBlob();
  const size = ImgApp.getSize(blob);

  // 2. Create new Google Slides with the custom page size using DocsServiceApp.
  const object = {
    title: "sample title", // Title of created Slides.
    width: { unit: "pixel", size: size.width },
    height: { unit: "pixel", size: size.height },
  };
  const presentationId = DocsServiceApp.createNewSlidesWithPageSize(object);

  // 3. Put the image and text.
  const s = SlidesApp.openById(presentationId);
  const slide = s.getSlides()[0];
  slide.insertImage(blob);
  slide.insertTextBox(text.text, text.left, text.top, text.width, text.height).getText().getTextStyle().setFontSize(text.fontSize);
  s.saveAndClose();

  // 4. Export the result image.
  const obj = Slides.Presentations.Pages.getThumbnail(
    presentationId,
    slide.getObjectId(),
    { "thumbnailProperties.thumbnailSize": "LARGE", "thumbnailProperties.mimeType": "PNG" }
  );
  const url = obj.contentUrl.replace(/=s\d+/, "=s" + size.width);
  const resultBlob = UrlFetchApp.fetch(url).getBlob().setName("Result_" + file.getName());
  sheet.getImages()[0].remove();
  sheet.insertImage(resultBlob, 2, 1);
  DriveApp.getFileById(presentationId).setTrashed(true);
}

Testing.

When this script is run, the following result is obtained.

Note:

• This script is a simple sample script for explaining this workaround. So please modify this for your actual situation.
• When you want to run the script using the trigger, please use the installable trigger.

References:

• DocsServiceApp
• ImgApp
• Inserting Text on Image using Google Apps Script