Скачать или смотреть How to Use Apps Script for Efficiently Comparing Google Drive File Names with Google Sheet Entries

Learn how to streamline your Google Drive file management by using Google Apps Script to compare file names between Drive and a Google Sheet. This guide walks you through the process with clear explanations and sample code.
---
This video is based on the question https://stackoverflow.com/q/70454029/ asked by the user 'Tim Welch' ( https://stackoverflow.com/u/16202242/ ) and on the answer https://stackoverflow.com/a/70476400/ provided by the user 'Tim Welch' ( https://stackoverflow.com/u/16202242/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Apps Script to Compare a list of file names in google drive to a list of names in a google sheet column to add new files from drive to sheet

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Streamline Your Google Drive Management with Apps Script

Managing files in Google Drive and keeping track of them in a Google Sheet can become overwhelming, especially as the number of files grows. If you are using Apps Script to pull file names and URLs from a Google Drive folder into a Google Sheet, you might have faced a common issue: how to prevent duplicate entries.

In this guide, we will walk through how to effectively compare the file names in your Google Drive against those already populated in a Google Sheet, ensuring that only new files are added. Let's get started!

The Problem

You may have already written a script that appends the full contents of a Google Drive folder to your designated Google Sheet. However, this existing script lacks the functionality to check if a file name already exists in the Sheet. Consequently, it inserts duplicates whenever you run the script.

The goal is to modify your script so it only appends new files to the Google Sheet.

The Solution

We will refine your existing Apps Script code to handle this problem effectively. Below is the final working code followed by an explanation of each part.

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Code

Defining the Function:

The function listFolderContents is created to initiate the process of comparing and updating the Google Sheet.

Accessing the Google Sheet:

The script accesses the active spreadsheet and specifically the sheet named 'index'. Ensure that you replace 'index' with the actual name of your Sheet.

Fetching Files and Existing Names:

The script retrieves all files in a specified Google Drive folder identified by its unique folder ID.

It then gathers all existing file names from the Google Sheet into an array using getValues() and flattens it to make searching easier.

Looping Through Drive Files:

A loop (while(contents.hasNext())) iterates through each file in the folder.

For each file, it extracts the name and URL.

Check for Duplicates:

The script checks if the file name already exists in the Sheet using includes(). If found, it simply returns, preventing duplication.

Appending New Files:

If the file name doesn’t exist, the script appends the name and link as a new row in the Sheet.

Important Notes

Folder ID: Make sure to replace the folder ID in the code with the one specific to your drive folder for accurate results.

Sheet Name: The sheet name in the script must match the actual sheet name in your spreadsheet to function correctly.

Conclusion

By utilizing the above Apps Script, you can efficiently manage your Google Drive files and ensure that your Google Sheet accurately reflects the contents without duplicates. This not only saves you time but also enhances your workflow by keeping your data clean and organized.

If you follow this guide step-by-step, you'll be able to adapt your code with ease. Happy scripting!