Is it possible to insert an image into a Google Sheets spreadsheet using a formula and Drive file path?

Question

Google Drive files are shared out via a unique (and random) URL when uploaded. Is there a way to upload an image via a Sheets formula that uses the file path of the image, NOT the sharing URL?

Instead of using: https://drive.google.com/file/(sharing link)

The formula would use something like: Drive/Test/img.png or Drive/Test/img.gif

I have noticed that within the help for the IMAGE function in Google Sheets it explicitly states that you cannot use images hosted at drive.google.com but I'd like to know if there's another way to accomplish this.

Not possible. See [here](https://stackoverflow.com/a/62127958/11551468) — Rafa Guillermo, Aug 18 '21 at 13:42
Thank you. Disappointing, but is what I figured. Can you post this as an answer so I can accept please? — Falcon4ch, Aug 18 '21 at 13:52

score 2 · Answer 1 · answered Aug 18 '21 at 15:18

If you want to get a specific image in a specific folder, you will have to explore your drive as multiple folders and multiple files can have the same name.

function listOfFilesOfFolder() {
  var myFolder = 'yourFolder';
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('yourSheet');
  sh.clear();
  sh.appendRow(["name", "date", "URL", "id", "type"]);
  var folders = DriveApp.getFoldersByName(myFolder)
  var foldersnext = folders.next();
  var data = [];
  var files = foldersnext.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    data = [ 
      file.getName(),
      file.getLastUpdated(),
      file.getUrl(),
      file.getId(),
      file.getMimeType()
    ];
    sh.appendRow(data);
  }
  sh.getRange('F1').setFormula(`={"image";arrayformula(if(D2:D="",,if(left(E2:E,5)="image",IMAGE("https://docs.google.com/uc?export=view&id="&D2:D),)))}`)
}

score 2 · Accepted Answer · answered Aug 18 '21 at 15:34

Unfortunately this isn’t possible. As Google Drive supports the existence of multiple files with the same name in the same folder, a file path isn’t enough to uniquely identify a file and so the file ID is required regardless of whether it’s ‘file path’ is unique or not.

Is it possible to insert an image into a Google Sheets spreadsheet using a formula and Drive file path?

2 Answers2