Import Google Drive Images into Spreadsheet #appsscript

Hello everyone. In this short video, I'll demonstrate how to import and insert images from a Google Drive folder into a Google sheet using app script. As you can see in this sheet, we have two columns, image and description. In the settings tab, you can specify the URL of the folder containing your images. To demonstrate, I will first clear this sheet. After specifying the folder URL, simply click the custom menu and then import images. This will automatically populate the current sheet with all the images from the specified Google Drive folder using Google Sheets image function to display them directly in the cells. Also notice how the title and the description are differently formatted in a single cell by utilizing app scripts rich text builder function. You might be wondering how these descriptions were added. Google Drive allows you to attach descriptions to your images. Let me show you how this works. Here you can see a dedicated field for adding descriptions. Now you might be thinking, did I have to manually open each image to add these descriptions? The answer is no. I've created a Google form connected to this spreadsheet which allows me to upload images along with their titles and descriptions in one go. And the form can be created from the custom menu. Let me show you the form. I'll demonstrate by uploading a new image. As you can see, the image has been uploaded to my Google Drive folder and the description has been attached to it. Now, let me show you how to set this up. First, open the spreadsheet from the link provided in the description below. Then, clicking the custom menu will reveal four options. Import images, create form, view, upload form, URL, and delete form. Here is the folder containing the images I want to import into the spreadsheet. We need to provide this folder's URL in the settings tab. As we've seen, clicking import and insert images will import all the images in the folder. If you need an image uploader form, click the create form menu to generate one. Let me show you. First I will delete the previously linked form. Then I will select this menu item. And the form has been created successfully. Let me open it. One thing we need to do here is change the question type to file upload as this cannot be done automatically by app script. Please let me know in the comments if there's a way to automate this. Now let's view the connected folder. We need to copy this folder's URL and paste it into the settings tab. After that, you can upload images and attach descriptions. Once the form is created, you can retrieve its URL by clicking view form URL. Note that you don't need this Google form if your images are already in the folder. I've provided it for cases where you want to upload images with descriptions. Now let's walk through the code. Open the script editor by going to extensions and then app script. This block defines the function which retrieves images from a specified Google Drive folder and populates a Google sheet. It starts by fetching the folder URL from cell B1 of the settings tab. The folder ID is extracted from the URL and if no URL is provided, it defaults to the root drive folder. It then iterates through all files in the folder, filtering for images based on their type. For each image, it constructs an image formula, retrieves the files name, description, or an empty string if none exists, and URL, and stores these in an array named image files. This block clears the existing content in the active sheet below the header row to prepare for new data. This block sets the image files array as the data in the active sheet. This block applies rich text formatting to the description column for each image row. It calls the create rich text function defined below passing the title and description to format the text with styled titles and descriptions. This block defines the create form function which creates a Google form for uploading images with titles and descriptions. It first checks if a form already exists by retrieving the form URL from document properties. If a form exists, it alerts the user with the form's URL and instructions to delete it before creating a new one. If no form exists, it creates a new form titled image upload form, adds fields for title, description, and a textbased image upload prompt, not a file upload due to app script limitations, and sets the active spreadsheet as the form's response destination. This block stores the newly created form's URL and ID and document properties for future reference. It also checks if the spreadsheet's header row matches the expected headers. If not, it sets the headers. Finally, it alerts the user with the form's URL and call setup trigger to set up a form submission trigger, which will then update image by attaching description to it. This block defines the update image function, which processes form submissions. It updates the files name and description based on the form inputs. This block defines the get upload form URL function. It fetches the form ID from document properties, gets the corresponding folder, and returns its URL via an alert. If no form exists or an error occurs, it displays an appropriate error message. This block defines the delete form function which deletes an existing form. That's all for the code overview. You can find the spreadsheet link with the attached app script code in the description below. If you enjoyed the video, please subscribe to my channel. Thanks for watching and see you in the next one.