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
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.
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.
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.
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.
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 to Google Sheets - In this video, I demonstrate how to Scrape Amazon Website to Google Sheets using software called ScraperAPI.
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 into One Google Sheet - In this post, I demonstrate how to Combine Multiple Spreadsheets into one Sheet using Google Apps Script.
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.
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.
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.
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.
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 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 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
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 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.
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 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.
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.
