4

I've set-up a spreadsheet as a DB to store catalog-like information. An important element of this information is an image the end user inserts in a user-front sheet using the Google Spreadsheet's menu option "Insert --> Image --> Image in cell". This image is then copied into the storage sheet using "SpreadsheetApp.CopyPasteType.PASTE_VALUES" in a script.

Now I'm crafting a document as an output of the information stored in the DB. The issue is that I can't parse the image from the spreadsheet to the document. I'm not even being able to get it from the spreadsheet.

I've surfed the web for a solution but got nothing. The closest I found is a 2 year-old similar question without a direct response: how to copy IMAGE from a google spreadsheet to a google document in script?

My logic says that if I pasted the image-in-cell as a value, I should be able to get it back with .getValue() but obviously is not the case. I've tried to get it as a blob with no luck. I'm not too familiar with blobs so probably I did it wrong.

Any idea will be much appreciated :)

// This is the code I used to put the image in the cell (THIS WORKS, JUST FOR CONTEXT)
  var fotosToCopy = cotizador.getRangeByName('CotizacionFotos');
  fotosToCopy.copyTo(destino.getRange(fila,2), 
  SpreadsheetApp.CopyPasteType.PASTE_VALUES, true);

// This is the code I'm trying to get the image from the cell (NOT WORKING)
  var fotosToCopy = origen.getRange(personajeRow,2).getValue(); //I've tried .getFormula() with no results; origen is the search range, personajeRow is the row to do the search and both work.

// This is what I'm using to put the image in the document
  var doc = DocumentApp.openById(cotizacionId); // cotizacionId is working
  var body = doc.getBody();
  var foto = body.getImages(); // I'll replace the only existing image
  var parent = foto[0].getParent();
  parent.insertInlineImage(parent.getChildIndex(foto)+1, fotosToCopy[0]); //fotosToCopy[0] is the issue, it returns "undefined"
  foto[0].removeFromParent();

Ideally, the existing in-line-image in the document should be replaced by the image-in-cell from the spreadsheet. The error I get is: "Execution failed: Cannot convert Array to Element".

Any idea?

shabnam bharmal
  • 589
  • 5
  • 13
Victor Chavez
  • 41
  • 1
  • 2
  • 2
    In the current stage, the image put in a cell cannot be retrieved using Google Apps Script. And also, even when the image is put on the sheet, the blob of image cannot be retrieved. So the images in the Spreadsheet cannot be used at other Google Docs. Although this might be modified in the future, unfortunately, this is the current answer. I'm sorry for this situation. – Tanaike Jun 09 '19 at 22:41
  • Thank you Tanaike, that at least points me in the right direction, a different one :( – Victor Chavez Jun 10 '19 at 23:03

1 Answers1

0

so I haven't coded this very cleanly but it should give you somehtign close to what you want, but first an explanation:

Google Sheets doesn't have the capacity to return the blob of an image, only a reference to the image, but Google Drive does.

My solution below takes the blob of the FIRST image in the Google Drive folder and adds it to the Document based on the DocID I provide to the function:

function retriveFolder(folderName,imageName,docID){

  var imageFolder = DriveApp.getFoldersByName(folderName).next();

  var firstImageinFolder = imageFolder.getFilesByName(imageName).next();

  var imageBlob =  firstImageinFolder.getBlob();

  DocumentApp.openById(docID).getBody().insertImage(1, imageBlob);

}

I hope this helps, best I could come up with when I had the same problem. Not ideal but should be a suitable workaround.

Meruva
  • 59
  • 10