0

I want to get text from an image placed in a cell using Google Apps Script. What code do I write in Google Apps Script?enter image description here

Can the cell be saved as an image and the text read?

HRGM
  • 13
  • 1
  • Here's a complete example: https://workspace.google.com/blog/developers-practitioners/getting-started-with-the-google-vision-api-from-gsuite – Cooper Feb 15 '23 at 16:22
  • Thanks for the response. but the image does not have a URL. It is only placed in a cell. – HRGM Feb 16 '23 at 00:10
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand the situation of `an image placed in a cell`. In order to correctly understand your situation, can you provide a sample Spreadsheet including it? – Tanaike Feb 16 '23 at 00:30
  • https://docs.google.com/spreadsheets/d/1J5Dy1-ao61PUbywjTr8d1Z_uud2iJRihHgB-1p-ua9U/edit?usp=sharing sample Spreadsheet – HRGM Feb 16 '23 at 06:22
  • Thank you for replying and providing the sample Spreadsheet. From your sample Spreadsheet, I proposed an answer. Please confirm it. – Tanaike Feb 16 '23 at 12:17

1 Answers1

1

I believe your goal is as follows.

  • You have a Google Spreadsheet where the images are put into the cells. In this case, the images are directly put into the cells without URLs and the built-in function. The images show some texts.
  • You want to retrieve the images and retrieve the texts from the images.

Unfortunately, in the current stage, there are no built-in methods for directly retrieving the images in the cells. So, in this case, I use this workaround for retrieving the images from the cells. And, in order to convert the texts from the image, Drive API is used.

When these are reflected in a sample script, it becomes as follows.

Usage:

1. Install a Google Apps Script library.

In this workaround, DocsServiceApp (Author: me) of a Google Apps Script library is used. Please install this library. You can see how to install it at here.

2. Sample script.

Please copy and paste the following script to the script editor of Spreadsheet. And, please set your sheet name.

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const spreadsheetId = ss.getId();
  const ar = DocsServiceApp.openBySpreadsheetId(spreadsheetId).getSheetByName(sheetName).getImages();
  const res = ar.map(({ range, image }) => {
    const tempId = Drive.Files.insert({ mimeType: MimeType.GOOGLE_DOCS, title: "temp" }, image.blob).id;
    const text = DocumentApp.openById(tempId).getBody().getText();
    Drive.Files.remove(tempId);
    return { cell: range.a1Notation, text: text.trim() };
  });
  console.log(res);
}
  • In this script, the images in the cells are retrieved using a library, and the images are converted to Google Documents. And, the text is retrieved from Google Documents.

3. Enable Drive API.

Please enable Drive API at Advanced Google services. Ref

4. Testing.

When the above script is run for your provided Spreadsheet, the following result can be seen in the log.

[
  {"cell":"A1","text":"098765431 ZYX"},
  {"cell":"A2","text":"1234567890 abcdef"},
  {"cell":"A3","text":"098765431 ZYX"},
  {"cell":"A4","text":"1234567890 abcdef"},
  {"cell":"A5","text":"1234567890 abcdef"},
  {"cell":"A6","text":"098765431 ZYX"},
  {"cell":"A7","text":"098765431 ZYX"}
]

Note:

  • In this answer, I propose a simple method. When your sample Spreadsheet is used, the processing time was about 30 seconds. If your actual Spreadsheet has a lot of images, it might be required to split the cells and use the script. Please be careful about this.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165