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





















