0

I am trying to have an app script that will put an image from an online url into a cell on my workbook.

I do this using the =IMAGE(<image url>) formula in my apps script.

Afterwards I use the .copyTo function on the same cells to remove the function but keep the image.

The problem is the image disappears when I do this. The only way for me to get it to work is if I have a random variable assignment in between the code where I set the image functions and where I do the copy-pasting. It makes no sense to me why the code works with this additional line.. any ideas?

function insertImage() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var data = sheet.getRange("A1").getValue()

  if (data < 2 ){


sheet.getRange("B1").setFormula('=image("https://lh3.googleusercontent.com/l6JAkhvfxbP61_FWN92j4ulDMXJNH3HT1DR6xrE7MtwW-2AxpZl_WLnBzTpWhCuYkbHihgBQ=w640-h400-e365")')
sheet.getRange("B2").setFormula('=image("https://lh3.googleusercontent.com/l6JAkhvfxbP61_FWN92j4ulDMXJNH3HT1DR6xrE7MtwW-2AxpZl_WLnBzTpWhCuYkbHihgBQ=w640-h400-e365")')
sheet.getRange("B3").setFormula('=image("https://lh3.googleusercontent.com/l6JAkhvfxbP61_FWN92j4ulDMXJNH3HT1DR6xrE7MtwW-2AxpZl_WLnBzTpWhCuYkbHihgBQ=w640-h400-e365")')

    var img = sheet.getRange("B3").getValue()  // doesn't work without this line

    sheet.getRange('B1').copyTo(sheet.getRange('B1'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    sheet.getRange('B2').copyTo(sheet.getRange('B2'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    sheet.getRange('B3').copyTo(sheet.getRange('B3'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

}
}

As long as the var img = .... is there, the code works. Removing it makes the images disappear.

I'd like to not have that line since it doesn't do anything and have the images remain in the cell.

Joshua Zastrow
  • 1,355
  • 4
  • 17
  • 32
  • You want to put an image to a cell without the formula. If my understanding is correct, how about this workaround? From your situation, I thought that because the image put by the formula is not reflected, ``PASTE_VALUES`` might not work. So how about modifying from ``var img = sheet.getRange("B3").getValue()`` to ``SpreadsheetApp.flush()``? You can see the document at [here](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#flush). If I misunderstand about your issue and this was not what you want, I'm sorry. – Tanaike Aug 16 '18 at 05:36
  • @Tanaike `SpreadsheetApp.flush()` did work in lieu of the the line I had in there already, and that is probably a better workaround than what I had! Thanks for that. I guess even better would be to get the images in the cells without calling a worksheet function, which eliminates the need to remove the formula in the first place. – Joshua Zastrow Aug 16 '18 at 05:52
  • Thank you for replying. I'm glad this was useful for your situation. As other workaround, how about using Sheets API? By using this, it is not required to use ``SpreadsheetApp.flush()``. I posted it as an answer, because the script includes an object. Could you please confirm it? – Tanaike Aug 16 '18 at 07:45
  • Possible duplicate of [Creating and Sending sheets in the same function](https://stackoverflow.com/questions/48304224/creating-and-sending-sheets-in-the-same-function) – tehhowch Aug 16 '18 at 11:02
  • Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, feel free to tell me. I would like to study to solve your issues. – Tanaike Aug 20 '18 at 22:31

1 Answers1

1

From my experiences, I thought that Sheets API might be able to be used for your situation. So when I tried it, I confirmed that it worked in my environment. So as other workaround, how about using Sheets API as follows? In this case, SpreadsheetApp.flush() is not required to be used.

In this script, the formula is put to a cell using Sheets API. And the value is copied using SpreadsheetApp.

Sample script :

When you use this script, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

var spreadsheetId = "### spreadsheetId ###";
var sheetId = "### sheetId ###";
var url = "### url ###";

var resource = {"requests": [{
  "updateCells": {
    "rows": [{"values": [{"userEnteredValue": {"formulaValue": "=image(\"" + url + "\")"}}]}],
    "range": {"sheetId": sheetId, "startRowIndex": 0, "endRowIndex": 1, "startColumnIndex": 1, "endColumnIndex": 2},
    "fields": "userEnteredValue.formulaValue"
  }
}]};
Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId);
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('B1').copyTo(sheet.getRange('B1'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

Note :

  • Before you run this script, please set spreadsheetId, sheetId and url.
  • In this script, UpdateCellsRequest is used. "startRowIndex": 0, "endRowIndex": 1, "startColumnIndex": 1, "endColumnIndex": 2 means "B1".
  • When copyPaste is included in the same request body with updateCells, the image was removed. I thought that this might be the same situation of your script. So I separated putting formula and copyPaste.

Reference :

If this was not what you want, I'm sorry.

Tanaike
  • 181,128
  • 11
  • 97
  • 165