CODEWITHSUNDEEP
imagegoogle-apps-scriptfile-upload
John Post
John Post

Reputation: 9

Auto Import Image in sheet?

I run my own fantasy baseball yearly projections and I'm looking to automatically add player images to a sheet I created.

Test Page

I use a formula to create a link to the player's image page on ESPN.

https://a.espncdn.com/combiner/i?img=/i/headshots/mlb/players/full/33859.png

Is there a way to automatically add the player image to the page?

=image("Link") is the formula, but i cant use the cell location as the link.

Any advise or guidance will be helpful and appreciated

Upvotes: 0

Views: 209

Answers (1)

Monique G.
Monique G.

Reputation: 239

I created an Apps Script method to achieve your target as it is not possible to do it using formula.

Here's the script I did with comments:

function SpecialOnEdit(e) {
  //Get the edited cell
  var range = e.range;
  //Get the active sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  //Download the image. (e.value is the URL in Google sheet)
  var response = UrlFetchApp.fetch(e.value);
  //Get the binary data of the image and create a blob object
  var binaryData = response.getContent();
  var blob = Utilities.newBlob(binaryData, 'image/png', 'MyImageName');
  
  //Insert the image into the same row that the user place the image URL link
  var image = sheet.insertImage(blob, range.getHeight(), range.getRow());
  //Set the height of the row, show that the image can fit the row
  sheet.setRowHeight(range.getRow(), image.getHeight() + 10);
}

You should add triggers next after pasting the script. Go to your Script Editor and click Edit->Current project's triggers->Add Trigger->under Select event type, select On edit->click Save

Next, to test the trigger and script, paste a URL of a PNG image format in the spreadsheet to validate the result.

Here's a sample output of the script and trigger:

enter image description here

Upvotes: 1

Related Questions