CODEWITHSUNDEEP
excelimagegoogle-apps-script
shenkwen
shenkwen

Reputation: 3880

Import image cells from excel to Google Sheet

I am trying to import some product datas from Excel to Google Sheet. There are some pictures in excel. If I simply copy and paste the pictures will be missing out. With Google Sheet's import function, the images will appear in Google sheet just as they do in excel.

However, most of the pictures are "over the cell". I want them to be "in the cell" so it is easier to manipulate the pictures later.

I don't know why SOME of the product pictures appear to be "in the cell" in google sheet. The screenshot below is from Excel, the two selected pictures are inserted into excel in the same way:

enter image description here

And this is a screenshot from Google Sheet:

enter image description here

You can see in the last row the picture is "in the cell". If there is a way to ensure the pictures to be "in the cell" after the import, my problem will be solved. But I can't find out how.

So I am thinking if I can use App Script to do this, the steps I can think of are:

  1. Loop through the images
  2. If they are "over the cell", detect which cell they are over
  3. Put the image into the cell

Is this possible at all?

A sample of my data: https://docs.google.com/spreadsheets/d/1OtlQNu7cKelnXBJk2gdCXHJkT0ixNugXfaBkQfQzxto/edit?usp=sharing

Upvotes: 0

Views: 2260

Answers (1)

Tanaike
Tanaike

Reputation: 201378

Issue and workaround:

In the current stage, unfortunately, there is no built-in method for directly retrieving the image in a cell using Google Apps Script. So, in this case, I would like to propose using DocsServiceApp (Author: me) of a Google Apps Script library. When this library is used, the image in a cell can be retrieved, and also it can confirm whether the image is put on a cell or into a cell. When this library is used as a sample script for achieving your goal, how about the following sample script?

Usage:

1. Install Google Apps Script library.

Please install DocsServiceApp of a Google Apps Script library. You can see how to install it at here.

2. Enable Drive API.

Please enable Drive API at Advanced Google services. Ref

3. Prepare sample script.

Please copy and paste the following script to the script editor of your sample Spreadsheet.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const obj = DocsServiceApp.openBySpreadsheetId(ss.getId()).getSheetByName("test").getImages();
  const sheet = ss.getSheetByName("test");
  obj.forEach(o => {
    if (!o.image.innerCell) {
      const temp = DriveApp.createFile(o.image.blob);
      const link = Drive.Files.get(temp.getId()).thumbnailLink.replace("=s220", "=s1000");
      sheet.getRange(o.range.a1Notation).setValue(SpreadsheetApp.newCellImage().setSourceUrl(link).build());
      temp.setTrashed(true);
    }
  });
  sheet.getImages().forEach(e => e.remove());
}
  • When this script is run, the images over the cells are put into the cells.

Note:

  • I tested my script using your provided sample Spreadsheet. If you change your Spreadsheet, this script might not be able to be used. Please be careful about this.

Reference:

Added:

From your following reply,

It works on my test sheet. However when I try to run it on my actual sheet, I am running into this error:

Although, unfortunately, I cannot replicate your situation, from your error message, I added one more sample script by guessing your current situation. Could you please confirm it?

Sample script:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = ss.getId();
  const tempSS = DriveApp.getFileById(ssId).makeCopy("temp");
  const obj = DocsServiceApp.openBySpreadsheetId(tempSS.getId()).getSheetByName("test").getImages();
  const sheet = ss.getSheetByName("test");
  obj.forEach(o => {
    if (!o.image.innerCell) {
      const temp = DriveApp.createFile(o.image.blob);
      const link = Drive.Files.get(temp.getId()).thumbnailLink.replace("=s220", "=s1000");
      sheet.getRange(o.range.a1Notation).setValue(SpreadsheetApp.newCellImage().setSourceUrl(link).build());
      temp.setTrashed(true);
    }
  });
  sheet.getImages().forEach(e => e.remove());
  tempSS.setTrashed(true);
}

Upvotes: 2

Related Questions