How Did I End Up with This Problem?
Google Drive has generously allowed developers to use it for hosting images and fetching them via a shareable link to embed in the browser until the beginning of 2024. Last year, when I was working on a self-fun project for “creating a gallery that could fetch content from a Google Sheet connected to a form for collecting responses,” I could publish the Google Sheet and use the URL to fetch all the data, which included text, URLs, and images via Google Drive links.
GitHub Repo (feel free to use the codes) GitHub Page (Notice that the images are showing as broken links now)
After this project, I was happy to have this solution at my disposal whenever I wanted to use it in the future. But to my surprise, when I actually needed to create a gallery for showcasing the works of students for the “Generative AI module,” it did not work. I thoroughly reviewed the codes, but everything seemed perfect. However, after checking online, I found out that Google has changed its policy for Google Drive-hosted images.
For a few days, I kept looking for a solution but couldn’t find one. I tried the following things:
- Creating a WordPress plugin that could fetch the contents from Google Sheets (I have actually managed to create a working plugin with the help of ChatGPT, but the problem remained the same. It couldn’t fetch the image content. I tried changing all MIME types and writing app scripts to change the Google Drive URLs to absolute paths, but nothing worked, and I finally gave up).
- Then I tried looking for existing plugins, free/paid, and ended up getting a very good one, but that also had issues in terms of fetching the images and displaying them as featured images. This was needed because I wanted to convert one form entry into a post and later make post grids, which would have been connected with a modal allowing viewing of the images and related text.
After trying for a few days, I accepted defeat and moved on to find a way to export data into some format that could be used for bulk post creation. At some point, I managed to pull the images too, but couldn’t set them as a featured image automatically. Manually doing it would have defied the objective, as the quality of entries was exhaustive.
The Workaround
I had to create the gallery anyway, so I focused on minimizing the effort of selecting the best submissions and doing it manually. Now, this required me to go through each and every submission in Google Sheets, check for the image by clicking on it or hovering and waiting for it to pop up and show the thumbnail. This was also consuming a lot of time. So, I started looking for a way to somehow use the URLs and fetch the thumbnails of the images and show them next to the URL in the same row.
I found out the =IMAGE(source, mode, height, width) formula, which could do the trick but it wasn’t working since it clearly mentioned that it would only support images not hosted on Google Drive.
I was disappointed and almost decided not to pursue this challenge anymore. I took a break from it and got engaged with other things. Two days later, the eureka moment came! While working on another problem, I stumbled upon a tiny article, which rekindled my desire to solve the previous problem. I realized that maybe Google won’t allow me to fetch the source image due to a change of policy, but what about the thumbnail of the image? I only needed to see a small preview of the URL images to make an informed decision about choosing that entry in the showcase.
The Solution
Well, if you’re thinking just replacing the word “thumbnail” with “open” in the Drive link would work, then you’re wrong! I was wrong too 🙂 at first. But then, after fiddling around, I have found the right method which I am sharing below.
Step 01
This is how the URL is formatted after form submission in Google Sheets (https://drive.google.com/open?id=1scgs5T8k8Big5tjjAdh0weaM591dqteH). Make a copy of all the URLs in the next column.
Step 02
We have to convert all the URLs into file chips. This can be done by going to Insert > Smart chips > Convert to file chips.
Step 03
Now we have to extract the url from file chips on the next column. To do that hover on the file chip in first row and click on Data Extraction
This shall open a sidebar with options to extract the URL to a target cell. Choose the cell next to the file chip cell and click on Extract. This will generate a URL like below:
https://drive.google.com/open?id=1zkDhy1XC7Q9uyQMKRPdq-Dq5QHA27sKu (This may look like the same URL, but it’s different because I tried directly using the =IMAGE() formula by replacing the “open” to “thumbnail,” but it did not work.
Step 04
Replace the word “open” with “thumbnail”. Use find and replace from the edit menu. Make sure to select only the range of extracted URLs, not the entire sheet.
This should look like this: https://drive.google.com/thumbnail?id=1zkDhy1XC7Q9uyQMKRPdq-Dq5QHA27sKu
Final Step
Use =IMAGE() formula to create thumbnails like below Learn more
This looks like a lot of work. However, it helped tremendously and saved a lot of time for me. You may find many other use cases for this solution. This hack works! Further, I am looking for a way to do this entire process with a single click via App Script. Will keep you guys posted if that happens. Thank you for your time.
