Adding CellImage from Google Sheets to a Google Doc

Question

I am trying to insert a CellImage taken from a Google Sheet into a newly created Google Doc using Apps Script.

I have a CellImage in a Google Sheet: [A Google Sheet with an image created by the formula:

=image("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=...)](https://i.sstatic.net/XHz9s.png)

I've created an Apps Script that is supposed to create a new Google Doc and insert the image from cell A1 into the Doc:

function myFunction() {
  let doc = DocumentApp.create("QR Holder");
  let sheet = SpreadsheetApp.getActiveSpreadsheet();
  let range = sheet.getRange("A1");
  let image = range.getValue();
  let url = image.getUrl();
  console.log(url);
  let blob = UrlFetchApp.fetch(url).getBlob();
  let body = doc.getBody();
  body.insertImage(0, blob);
}

Execution log

11:01:18 AM Notice  Execution started
11:01:21 AM Info    
11:01:20 AM Error   
Exception: Attribute provided with no value: url
myFunction  @ Code.gs:8

This shows that the url variable does not store a valid URL and the blob is not properly fetched.

Answer 1

Modification points:

• Unfortunately, the URL cannot be retrieved from image of let image = range.getValue(); with getUrl(). In this case, it is required to retrieve the URL from the formulas retrieved with getFormula.

• In your sample image, it seems that the formula is =image("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=...')". In this case, the last ) might not be required to be used. But, I'm not sure whether this is your expected situation.

When these points are reflected in your script, how about the following modification?

From:

let image = range.getValue();
let url = image.getUrl();

To:

let url = encodeURI(range.getFormula().replace(/\=image\("(.*)"\)/i, "$1"));

Reference:

• getFormula()