New Image Function

[Anthony Williams]

Is it possible to reference images stored in a OneDrive folder?

I've tried copy the link from the OneDrive image and using

which gets me #CONNECT!

Any ideas

Thanks

[#2]

I believe you'll need to include the extension of the image in the URL. IMAGE supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP.

[#3]

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.
Thanks

[#4]

@Anthony Williams 

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.

[#5]

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.

[#6]

Good to know about General release. I've been using IMAGE since August, but my work computer has not received IMAGE yet. I have a workbook with several hundred image URLs and IMAGE pulls in the pictures without issue. There's a bit of a lag in navigation when all pictures are showing so I've created a show/hide toggle. The workbook is about 250 kb with no pictures showing and 15 mb with pictures showing. All in all, IMAGE is a cool new feature.

[#7]

Thanks for your help - Just tried Microsoft Excel support - completely useless. Maybe MS are still testing the links to OneDrive - I'll keep an eye on it AND try to solve it another way, it just would have been perfect for me.

[#8 · Accepted solution]

@Anthony Williams 

Any site which requires authentication, includes OneDrive, doesn't work with IMAGE().

[#9]

@Sergei Baklan Thank you. It not the answer I want, but you have saved me a lot of head scratching :grinning_face:. Back to the drawing board. It was going to be exactly what I have been waiting for.

[#10]

@Anthony Williams 

Sorry if I misunderstood the question, I guess you asked does it work with OneDrive or not.

[#11]

You answered my question perfectly
I didn't make myself clear, I was trying to say you have saved me a lot of time trying to make something that doesn't work, work

[#12]

I did find a solution from YouTube. https://www.youtube.com/@MyOnlineTrainingHub.
In OneDrive if I use the Embed option on the image, and then use the Generate to create the HTML code. I've then copied https address to a spreadsheet cell, and then using that cell reference in the IMAGE function. This is a lot of work but does work.
The hover over the image which in the beta version popped up a larger image doesn't work on my spreadsheet. Others have suggested that this feature was removed on the release version.
Thanks to all.

[#13]

@Anthony 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%21306...

Then if I right-click your image and select "Copy Image Address", I get a different URL again:

https://am3pap003files.storage.live.com/y4mkekQgEZTA1704VWjJHitslT58O4r36wAYympVEqUaw3Dv5ceBGnM1JKxi...

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.

[#14]

@Trampoline 

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.

[#15]

@phand2390 

I tried using this image function in one drive.I didnt work.But fortunately one morning i was able to render the images submitted from form.But this lasted for only 2 hours

 after that how much ever i try, i am not able to render the images..

We can render images in local D: and shared folders.

I feel we should use google sheets to get the solution.

If any one has a different solution, please share the same.

Rgds

Vinod

[#16]

I was able to successfully use the excel IMAGE function when using a png file located on our companies SharePoint website. The png file location is authorization based and the account I use in excel is the same account that has access to the sharepoint website.
I used the use "view original" url which is available after clicking on the image file and selecting the three dots at the top.

Hope this helps.

[#17]

@Anthony Williams Here is the solution for pulling images from onedrive:
https://youtu.be/Z5HcAK5vNbk

[#18]

Here is the solution for pulling images from onedrive:
https://youtu.be/Z5HcAK5vNbk

[#19]

Very helpful. Thanks