Google Sheet/Google Script - Plotting points over a picture

Question

I've been googling this a lot but I couldn't find any answer.

I have a sheet like this:

| ID | Name | X Coord. | Y Coord. |

I have a custom picture (it's basically a map) 512x512 and I need to plot the points based on the X/Y Coords.

Is there any way to do that on Google Sheet? I'm open to any possible solution (even the ones including html)

Thanks in advance!

EDIT.This is what I need:

My sheet

| ID | Name | X Coord. | Y Coord. |
| 1  | AAA  |    0     |    0     |
| 2  | BBB  |   512    |   512    |
| 3  | CCC  |   256    |   256    |

My picture

Answer 1

I believe your current situation and your goal as follows.

• You have an image of 512 x 512 pixels.
  • The image file is put in your Google Drive.
• You want to plot the points on the image by the coordinates retrieved from the columns "C" and "D" in the Spreadsheet.
• You want to put the texts retrieved from the column "B".
• You want to achieve this using Google Apps Script.

Issue and workaround:

Unfortunately, in the current stage, there are no methods for directly editing the image and putting the texts and shapes in the methods for Google Apps Script. So in this case, it is required to use the workaround.

Fortunately, I have published a report about "Inserting Text on Image using Google Apps Script" in my blog. Ref I thought that in your situation, this might be able to be used as a workaround. The flow of this workaround is as follows.

1. Retrieve blob from an image file.
2. Retrieve the image size.
   • In this case, a Google Apps Script library of ImgApp is used.
3. Create new Google Slides with the page size which is the same with the image size.
   • In this case, a Google Apps Script library of DocsServiceApp is used.
4. Insert the image.
5. Retrieve the coordinates from Spreadsheet.
6. Plot the points using the coordinates to Google Slides.
7. Retrieve the image blob from Google Slides.

Usage:

In this case, it supposes that you have already had the Google Apps Script project. If you have no Google Apps Script project, please create it. For example, you can also create it from the script editor on Google Spreadsheet.

1. Install Google Apps Script libraries.

Please install the following Google Apps Script libraries. You can see the official document for installing the library at here.

1. ImgApp
   • You can see the project key for installing the library at here.
2. DocsServiceApp
   • You can see the project key for installing the library at here.

2. Enable APIs:

Please enable Google Slides API at Advanced Google services.

3. Sample script:

Please copy and paste the following script to the script editor of Google Apps Script and please set the variables.

function myFunction() {
  const fileIdOfImage = "###"; // Please set the fileID of image.
  const spreadsheetId = "###"; // Please set the Spreadsheet ID of image.
  const sheetName = "Sheet1"; // Please set the sheet name.
  const outputFilename = "sample.png"; // Please set the output image filename.

  // 1. Retrieve blob from an image file.
  const blob = DriveApp.getFileById(fileIdOfImage).getBlob();

  // 2. Retrieve the image size.
  const { width, height } = ImgApp.getSize(blob);

  // 3. Create new Google Slides with the page size which is the same with the image size.
  const object = { title: "temp", width: { unit: "pixel", size: width }, height: { unit: "pixel", size: height } };
  const id = DocsServiceApp.createNewSlidesWithPageSize(object);

  // 4. Insert the image.
  const s = SlidesApp.openById(id);
  const slide = s.getSlides()[0];
  const image = slide.insertImage(blob);

  // 5. Retrieve the coordinates from Spreadsheet.
  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  const values = sheet.getRange("B2:D" + sheet.getLastRow()).getValues();

  // 6. Plot the points using the coordinates to Google Slides.
  const pointSize = 8;
  const fontSize = 10;
  const boxSize = 60;
  values.forEach(([v, x, y]) => {
    let px = (x / 1.33333) - (pointSize / 2);
    let py = (-1 * (y - height) / 1.33333) - (pointSize / 2);
    slide.insertShape(SlidesApp.ShapeType.ELLIPSE, px, py, pointSize, pointSize).getFill().setSolidFill("#ff0000");
    const textBox = slide.insertTextBox(v, px - (boxSize / 2) + (pointSize / 2), py - (boxSize / 2) + (pointSize / 2), boxSize, boxSize);
    textBox.setContentAlignment(SlidesApp.ContentAlignment[(px < 0 && py > 0) || (px > 0 && py > 0) ? "TOP" : "BOTTOM"]);
    const text = textBox.getText();
    text.getParagraphStyle().setParagraphAlignment(SlidesApp.ParagraphAlignment[(px > 0 && py < 0) || (px > 0 && py > 0) ? "START" : "END"]);
    text.getTextStyle().setFontSize(fontSize);
  });
  slide.group([image, ...slide.getShapes()]);
  s.saveAndClose();

  // 7. Retrieve the image blob from Google Slides.
  const obj = Slides.Presentations.Pages.getThumbnail(id, slide.getObjectId(), {"thumbnailProperties.thumbnailSize": "LARGE"});
  const outputBlob = UrlFetchApp.fetch(obj.contentUrl.replace("=s1600", "=s" + width)).getBlob().setName(outputFilename);
  DriveApp.createFile(outputBlob);
  DriveApp.getFileById(id).setTrashed(true);
}

• When above script is run, the image blob is retrieved from the image file of fileIdOfImage, and retrieve the coordinates for plotting the points from Spreadsheet of spreadsheetId and sheetName. And then, those are merged and output as an image file of outputFilename.
• When you want to change the font size and the point size, please adjust the variables of fontSize, pointSize and boxSize.

Sample input and output images.

Sample input data.

Spreadsheet:

Image:

This image size is 512 x 512 pixels.

Sample output data.

When above input data is used with above sample script, the following result is obtained. The origin of this plot is the lower left. This is from your sample image.

Note:

• This is a simple sample script. So please modify this for your actual situation.

References:

• Google Apps Script libraries
  • ImgApp
  • DocsServiceApp
• Google Slides API