Forum Discussion
New Image Function
Any site which requires authentication, includes OneDrive, doesn't work with IMAGE().
- Like this
=IMAGE("https://1drv.ms/u/s!AmbSxIyCxe5hktkARJfWxindUUDxyQ?e=OW2Gjb.jpg") - this didn't work. Have put the file type in the correct place?
I tried pasting this (Below) into a cell, and that took me to the correct image in my OneDrive
https://1drv.ms/u/s!AmbSxIyCxe5hktkARJfWxindUUDxyQ?e=OW2Gjb
I want to be able to have the image in the cell.
ThanksAnthony Williams I wonder if the problem here is that you're using a shortened URL in your IMAGE formula as opposed to a link which points directly to the picture itself (and not your OneDrive folder).
I have successfully used IMAGE to link to pictures which are stored on our company Sharepoint. Not exactly the same as OneDrive, but similar. The key was finding the direct URL to the picture.
When I click on your image link it opens up a full-length URL as follows:
https://onedrive.live.com/?authkey=%21AESX1sYp3VFA8ck&cid=61EEC5828CC4D266&id=61EEC5828CC4D266%21306304&parId=61EEC5828CC4D266%21306333&o=OneUp
Then if I right-click your image and select "Copy Image Address", I get a different URL again:
https://am3pap003files.storage.live.com/y4mkekQgEZTA1704VWjJHitslT58O4r36wAYympVEqUaw3Dv5ceBGnM1JKxi8Xmd8xozlkAh626EeDD52rLElaz5QjmcXBsaQlWcZoe-5jjhybsVdABeWtata5eYQ7GYpguF4LVKF7NgktFkGKD4j3V2KVqncZ26GU16cmjaQTbcW98aI7ZSSY48KMoDGDaiAHzdaqf0nZaL3jutd1QmiDcTQ/P1150415.JPG?psid=1&width=805&height=804
Unfortunatly this image address URL seems to be too long to work with the IMAGE function.
Do you have access to any Sharepoint folders which you could use instead? These might result in shorter, usable image URLs.
In my case, the Sharepoint URL is formatted as follows:
https://mysharepointsite.sharepoint.com/sites/mypagename/myfoldername/mypicture.png
This IMAGE formula has changed my life by the way, it's fantastic !!! Hope you get it working.
Must the image in question be made public? I have ensured that the image followed the format you wrote but still showing #connect or #busy in excel.
I tried it using a OneDrive URL and the local OneDrive path equivalent - no good.
The bolded link may be coming into play here. Since IMAGE is still exclusive to the Beta Channel (Insider), I take this as 'it might work' but we're going to be safe and list it as not supported.
Insert images in cells with the IMAGE function in Excel (office.com)
Known issues
- If the URL to the image file you are using is pointing to a site that requires authentication, the image will not render.
- Zooming in and out with images in cells may distort the images.
- Moving between platforms (for example, Windows and Mac) may result in irregular image rendering.
- Thanks - The IMAGE function is now on general release - I'm not an Insider, and got the function today. Watching early insider YouTube videos (Just) mention that it doesn't work referencing OneDrive - but hoped that would be changed before general release. Out of interest I tried Google Sheets and their version of IMAGE doesn't allow reference to GoogleDrive.
I may have to think of another way to get images into my spreadsheet - I was so hopeful of the new function. It appeared to be a far better solution than I've used before.
