Automatically Generate File IDs from Filenames in Google Sheets

Description

This Apps Script function allows you to automatically generate file IDs for filenames listed in a Google Sheet. By using this script, you can easily retrieve the unique identifiers (IDs) of files stored in Google Drive and update your spreadsheet with these IDs. This is especially useful for managing large inventories of files where each file needs to be uniquely identified.

Instructions

Create a Google Sheet named fileIdFromFileName. Populate the sheet with the following columns: Column A: ID (optional, can be left blank or used for unique identifiers), Column B: Filename (list the filenames of the files you want to retrieve IDs for), Column C: File ID (this column will be populated by the script).

Add the Script by opening your Google Sheet, going to Extensions > Apps Script, deleting any code in the script editor, and replacing it with the script provided below.

Run the Script by clicking on the play button (▶️) in the Apps Script editor. The script will search for the files in your Google Drive and populate Column C with the corresponding file IDs. If a file is not found, the script will set the cell to "File Not Found."

function createFileIds() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("fileIdFromFileName");
    
    // Get the range of values in column B starting from row 2 to the last row with content
    var filenames = sheet.getRange("B2:B").getValues().flat().filter(String);
    
    // Iterate through each filename
    for (var i = 0; i < filenames.length; i++) {
        // Get the filename
        var fileName = filenames[i];
        
        // Search for files in Google Drive with the given file name and get the first result
        var files = DriveApp.getFilesByName(fileName);
        
        // Check if file with filename exists
        if (files.hasNext()) {
            var file = files.next();
            
            // Get the unique identifier (ID) of the found file
            var fileId = file.getId();
            
            // Set the value of the cell in column C corresponding to the current filename
            sheet.getRange("C" + (i + 2)).setValue(fileId); // Adjusted row number
        } else {
            // If file with filename does not exist, set the corresponding cell in column C to "File Not Found"
            sheet.getRange("C" + (i + 2)).setValue("File Not Found"); // Adjusted row number
        }
    }
}