How to Search Google Drive on Google Sheets using Apps Script

In this post, I demonstrate how to search Google Drive by File Name on Google Sheets using Apps Script.

How to Video:

Video Notes:

Code in Video:

function SearchFileForm()
{ 
  var form = HtmlService.createHtmlOutputFromFile('SearchFile').setTitle('Search Files');
  SpreadsheetApp.getUi().showSidebar(form);
}

function addMenu()
{
  var menu = SpreadsheetApp.getUi().createMenu('File Search');
  menu.addItem('Search Files', 'SearchFileForm');
  menu.addToUi(); 
}

function onOpen(e)
{
  addMenu(); 
}

function searchFiles(title) 
{
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var filesSheet = ss.getSheetByName("Files");
  var lastRow = filesSheet.getLastRow()+1;
  filesSheet.getRange('A2:F'+lastRow).clear();
  var foundRecords = 'false';

  var files = DriveApp.searchFiles("title contains '"+title+"' ");
  while (files.hasNext()) 
  {
    var file = files.next();
    var fileName = file.getName();
    var fileLastUpdated = file.getLastUpdated();
    var fileCreated = file.getDateCreated();
    var fileURL = file.getUrl();
    var fileMime = file.getMimeType();
    var fileParents = file.getParents();
    var firstFolderName = '';
    while (fileParents.hasNext()) {
      var firstFolderName = fileParents.next().getName();
    }
    if(firstFolderName != '')
    {
      filesSheet.appendRow([fileName, 
                            fileURL, 
                            fileCreated, 
                            fileLastUpdated, 
                            fileMime, 
                            firstFolderName]);
    }
    foundRecords = 'true';
  }

  if(foundRecords == 'true')
  {
    return "<span style=\"font-weight: bold\" >Found Records</span>";
  }
  else
  {
    return "<span style=\"font-weight: bold\" >No Records Found</span>";
  }
  
}
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
<link rel="stylesheet" 
href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.6.1/css/bootstrap.min.css"  />
<script>
function SubmitRecord()
{
  document.getElementById("displayReturn").innerHTML = "";
  var title = document.getElementById("title").value;
  google.script.run.withSuccessHandler(returnBack).searchFiles(title);
}

function returnBack(stringBack)
{
  document.getElementById("displayReturn").innerHTML = stringBack;
}
 
</script>
  </head>
  <body>
  <form>
  <div style="padding: 10px" >
  <div class="form-row">
  <div class="form-group col-md-6">
  <label for="title">Title</label>
  <input type="text" id="title" class="form-control" />
  </div> 
  </div>
  <div class="form-row">
  <div class="form-group col-md-6">
  <input type="button" value="Submit" onclick="SubmitRecord()" class="btn btn-primary" />
  </div> 
  </div>
  </form>
  <div id="displayReturn" ></div>
  </body>
</html>

Related Posts

Back Up Google Drive Files How to Back Up Google Drive Files using Apps Script - In this post, I demonstrate how to back up Google Drive Files using Google Apps Script with Google Sheets.
Embed Chart Google Sheets How to Embed Charts on Google Sheets using Apps Script - In this post, I demonstrate How to Create Charts on Google Sheets using Google Apps Script.
Create Custom Menu Google Sheets How to Create a Menu on Google Sheets using Apps Script - In this post, I demonstrate how to Create a Customer Menu on Google Sheets using Google Apps Script.
Get and Set Value Google Sheets How to Get and Set Values on Google Sheets using Apps Script - In this post, I demonstrate how to Get and Set Values using Google Apps Script on Google Sheets.
Create Data Validation Google Sheets How to Create Data Validation on Drop Down using Google Apps Script - In this post, I demonstrate How to Create Data Validation Drop-down List Using Google Apps Script on Google Sheets.
Stock Trend Analysis Google Sheets Create Stock Trend Analyzer on Google Sheets - In this video, I demonstrate how to analyze stock using the GOOGLEFINANACE Function on Google Sheets with Google Apps Script.
Scrape Amazon Website Googe Sheets Scrape Amazon Website to Google Sheets - In this video, I demonstrate how to Scrape Amazon Website to Google Sheets using software called ScraperAPI.
Alert Popups on Google Sheets Create Alert Popups on Google Sheets - In this post, I demonstrate how to create 3 different types of alert popups using Google Apps Script on Google Sheets.
Combine Multiple Spreadsheets Google Sheets Combine Multiple Spreadsheets into One Google Sheet - In this post, I demonstrate how to Combine Multiple Spreadsheets into one Sheet using Google Apps Script.
Last Row and Column Google Sheets How to Get Next Open Row and Column using Google Apps Script - In this post, I demonstrate how to get the next open row and column using Google Apps Script on Google Sheets.
Time and Distance Map Google Sheets How to Get Map Time and Distance using Google Apps Script - In this post, I demonstrate how to get the Map Time and Distance between locations using Google Apps Script using the Map Class.
Create Dependent Dropdown Google Sheets How to Create Dependent Dropdown on Google Sheets - In this post, I demonstrate how to Create a Dependent Dropdown list on Google Sheets using Google Apps Script.
Call Rest API Google Sheets How to Call Rest API on Google Sheets - In this post, I demonstrate how to call a Rest API using Google Apps Script and Displaying the data on a Google Sheet.
Google Doc From Apps Script How to Create Google Doc from Apps Script - In this post, I demonstrate how to create a Google Doc using Google Apps Script and save it in Google Drive. In this example, I use the data from a Google Sheet.
How to Clear, Delete, Insert, and Replace Row Google Sheets How to Clear, Delete, Insert, and Replace Row using Google Apps Script - In this post, I demonstrate how to clear, delete, insert, and replace rows using Google Apps Script on Google Sheets.
Email Last Update Google Sheet Email Last Update on Google Sheet - In this post, I demonstrate how to email the last update made on a google sheet. This is a way to monitor any changes made on Google Sheets
JSON Web Service Google Sheets How to Call JSON Web Service to Populate Google Sheet - In this post, I demonstrate how to Call a JSON Web Service and Populate a Google Sheet with a table of raw data.
Scrape Website Google Sheet Scrape Website Data to Google Sheets - In this post, I demonstrate how to Scrape data from a Website and import it to a Google Sheet. I used JavaScript and Reg Expressions to accomplish the task.
Query Function Google Sheets Create Report Builder on Google Sheets using QUERY Function - In this post, I demonstrate how to use the QUERY function and Google Apps Script and build multiple report views from a list.
Copy Row Google Sheet to Google Sheet Copy Row from Sheet to Sheet using Apps Script on Google Sheets - In this post, I show how to copy a row of data from one sheet to another using Google Apps Script. This video also shows how to search a column on Google Sheets.
Updated Date Column Google Sheet Create Updated Date Column on Google Sheets - In this post, I demonstrate how to create an Updated Date Column on a Google Sheet. I use the onEdit(e) Google Apps Script function to accomplish the task.
How to Create Google Sheets Button to Run Apps Script - In this post, I show how to create a Button in Google Sheets as well as how to call a Googe Apps Script Function with the Button.