Problem: To show thumbnails of images hosted on Google Drive in the Google Sheets

Estimated reading time: 7 minutes

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:

  1. 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).
  2. 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.

Effortlessly Convert Word Documents to PDFs in Google Drive: A Simple Guide for Everyone

Estimated reading time: 6 minutes

Introduction:

Have you ever found yourself drowning in a sea of Word documents, wishing there was an easy way to convert them all into PDFs without the hassle of manual labor? Whether you’re tidying up your digital workspace, preparing documents for sharing, or simply looking to make your files more accessible and secure, we’ve got a solution that can save you hours of tedious work. In this post, we’ll walk you through a simple, automated process to convert your Word documents to PDFs directly within Google Drive, tailored for those who might not have a technical background.

Questions this will answer for you!

  1. Do you often struggle with converting multiple Word documents into PDFs, wishing there was a way to automate this time-consuming task?
  2. Have you ever needed to quickly convert a batch of job applications, project proposals, or class assignments from Word to PDF, but found yourself bogged down by the process?
  3. Are you looking for an efficient way to manage your files on Google Drive, especially when it comes to ensuring your documents are in the right format for sharing or archiving?
  4. Do you wish to leverage the power of automation to streamline your document management process but don’t know where to start?

The Solution: Simplifying Conversion with Google Apps Script

Converting Word documents to PDFs in Google Drive doesn’t have to be a manual or daunting task. By using Google Apps Script, a potent yet accessible automation tool provided by Google, you can easily set up a script that does the heavy lifting for you. This guide will introduce a straightforward script that automatically converts any Word documents in a specified Google Drive folder into PDFs, storing them neatly in a designated folder.

Step-by-Step Guide for Non-Tech Users:

  1. Setting Up Your Google Drive: Begin by organizing your Word documents in a specific folder within Google Drive. This will be your source folder where the script will look for files to convert.
  2. Creating the Google Apps Script: We’ll guide you through creating a new script project directly from Google Drive, a process that’s as simple as creating a new document.
  3. Scripting Made Easy: No need to worry about coding; we’ll provide you with a ready-to-use script. All you need to do is copy and paste it, with clear instructions on where to customize the folder names.
  4. Running Your Script: With just a click of a button, your script will start converting all the Word documents in your source folder into PDFs, automatically saving them in a specified target folder.
  5. Enjoying the Benefits of Automation: Sit back and see how this simple automation saves you hours, allowing you to focus on more important tasks.

Step 1: Ensure Your Word Documents are in Google Docs Format

First things first, Google Drive shines when working with its native formats (Google Docs, Sheets, etc.). To smoothly convert your Word documents to PDFs, they should be in Google Docs format. Here’s how to check and convert:

  • Check Format: Double-click a Word document in Google Drive. If it opens in Google Docs, you’re all set. If it downloads or prompts you to download, it’s still a Word document.
  • Convert to Google Docs: Right-click the Word document in Google Drive, select “Open with,” then choose “Google Docs.” This creates a new document in Google Docs format.

Tip: To automate this for future uploads, adjust your Google Drive settings to automatically convert uploads to Google Docs format. Find this option in Drive settings under “General” > “Convert uploads.”

Step 2: Use Google Apps Script to Convert Documents

Google Apps Script is a powerful tool that can automate tasks in Google Drive, including converting documents. Don’t worry; you won’t need to write any code yourself. Just follow these simple steps:

  1. Create a New Script:
    • In Google Drive, right-click and select “New” > “More” > “Google Apps Script.”
    • Title your new project something descriptive, like “Doc to PDF Converter.”
  2. Copy and Paste the Script:
    • Erase any code in the script editor and copy-paste the following script:
  • Replace 'Your Source Folder' with the name of your folder containing the Word documents.
  1. Run the Script:
    • Click the play button (▶) in the toolbar. The first time you do this, Google will ask for permissions. Review and accept them.
    • If the run button is greyed out, make sure you’ve selected the function convertDocsToPDF in the dropdown next to the play button.

Step 3: Locate Your PDFs

After running the script, find a new folder in your Google Drive named “PDFs” (or whatever you named it), containing the converted documents. Voilà! Your documents are now accessible as PDFs.

Conclusion:

There you have it—a straightforward, no-fuss method to convert Word documents to PDFs within Google Drive, no tech expertise required. This process can streamline your document management, making sharing and viewing documents a breeze across different platforms and devices. Embrace the digital ease, and let Google Drive do the heavy lifting for you.

Happy converting!