Goal
This tutorial shows how to search the files in your Google Drive and display the results and how to import from and export to CSV files which are saved in your Google Drive.
Time to Complete
Approximately 10 minutes
Prerequisites
Before you begin this tutorial, you should already be familiar with:
- Using the script editor and have experience using basic spreadsheet functions. See the overview of Apps Script.
- Defining custom menus in Google Sheets. See the guide to creating menus.
Index
This tutorial is divided into the following sections:
- Section 1: Searching your Google Drive and displaying matching files
- Section 2: Importing data from a CSV file.
- Section 3: Saving a selected Range to a CSV file.
- Summary
Section 1: Searching your Google Drive and displaying matching files
In this section, we assume have some files stored in your Google Drive. We will write a script to create a custom menu, search through the files for a user-supplied search term, and display the results in a Google Sheet.
- Create a new Google Sheet, and open the script editor (Tools > Script editor)
- Copy, paste, and save the following code. The
onOpen
function will run when the spreadsheet is opened and will add a custom menu named Search Google Drive to your spreadsheet’s menu bar. The custom menu has one option Search in all files, which will run the function namedsearch
.function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var searchMenuEntries = [ {name: "Search in all files", functionName: "search"}]; ss.addMenu("Search Google Drive", searchMenuEntries); } function search() { // Prompt the user for a search term var searchTerm = Browser.inputBox("Enter the string to search for:"); // Get the active spreadsheet and the active sheet var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); // Set up the spreadsheet to display the results var headers = [["File Name", "File Type", "URL"]]; sheet.clear(); sheet.getRange("A1:C1").setValues(headers); // Search the files in the user's Google Drive for the search term // See documentation for search parameters you can use // https://developers.google.com/apps-script/reference/drive/drive-app#searchFiles(String) var files = DriveApp.searchFiles("title contains '"+searchTerm.replace("'","\'")+"'"); // create an array to store our data to be written to the sheet var output = []; // Loop through the results and get the file name, file type, and URL while (files.hasNext()) { var file = files.next(); var name = file.getName(); var type = file.getMimeType(); var url = file.getUrl(); // push the file details to our output array (essentially pushing a row of data) output.push([name, type, url]); } // write data to the sheet sheet.getRange(2, 1, output.length, 3).setValues(output); }
- Save and reload your spreadsheet so that the Search Google Drive menu appears in your menu bar. Select Search Google Drive then Search in all files and enter a search term. You should see the results displayed in your spreadsheet.
Section 2: Importing data from a CSV file
In this section, we assume you have one or more CSV files saved in your Google Drive. We’ll write code to import a CSV file from your Google Drive into a spreadsheet.
- In Google Drive, open the spreadsheet you just saved, then click Tools > Script editor).
- Replace the existing
onOpen
function with the following code.function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var searchMenuEntries = [ {name: "Search in all files", functionName: "search"}]; var csvMenuEntries = [{name: "Load from CSV file", functionName: "importFromCSV"}]; ss.addMenu("Search Google Drive", searchMenuEntries); ss.addMenu("CSV", csvMenuEntries); }
- Copy the following code, and paste it at the bottom of your script. The
importFromCSV
function prompts the user for the name of the CSV file, and then searches through their Google Drive to find the matching file. It then calls theparseCsv
Utiltity, which is a handy tool for converting CSV data to a Javascript array.function importFromCSV() { var fileName = Browser.inputBox("Enter the name of the file in your Google Drive to import (e.g. myFile.csv):"); var searchTerm = "title = '"+fileName+"'"; // optionally you might want to specify that you are looking for a .csv by file type using: // var searchTerm = "title = '"+fileName+"' mimeType = 'text/csv'"; // read more about searching for files at // https://developers.google.com/apps-script/reference/drive/drive-app#searchFiles(String) // search for our file var files = DriveApp.searchFiles(searchTerm) var csvFile = ""; // Loop through the results while (files.hasNext()) { var file = files.next(); // assuming the first file we find is the one we want if (file.getName() == fileName) { // get file as a string csvFile = file.getBlob().getDataAsString(); break; } } // parseCsv will return a [][] array we can write to a sheet var csvData = Utilities.parseCsv(csvFile); var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); // boom data to a sheet sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData); }
- Save your code and reload your spreadsheet, so that the CSV menu appears in your menu bar. Select CSV then Load from CSV file and enter the file name to import. You should see the contents of the CSV file appear in your spreadsheet. For example, if you had the following CSV data saved in a file in your Docs List, then you would see the results like those shown in the image below.
First Name,Last Name,Department,Extension Jane,Smith,Engineering,6113 John,Doe,Marketing,1877 Sam,Jones,Sales,2233
Section 3: Saving a selected Range to a CSV file
In this final section we add code to save a selected Range in a spreadsheet to a CSV file in your Google Drive.
Note: This section uses the DriveApp.createFile() method.
- In Google Drive, open the spreadsheet you just saved, then click Tools > Script editor.
- Replace the existing
onOpen
function with the following code.function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var searchMenuEntries = [ {name: "Search in all files", functionName: "search"}]; var csvMenuEntries = [{name: "Save as CSV file", functionName: "saveAsCSV"}, {name: "Load from CSV file", functionName: "importFromCSV"}]; ss.addMenu("Search Google Drive", searchMenuEntries); ss.addMenu("CSV", csvMenuEntries); }
- Copy the following code, and paste it at the end of your script..
function saveAsCSV() { // Prompts the user for the file name var fileName = Browser.inputBox("Save CSV file as (e.g. myCSVFile):"); // Check that the file name entered wasn't empty if (fileName.length !== 0) { // Add the ".csv" extension to the file name fileName = fileName + ".csv"; // Convert the range data to CSV format var csvFile = convertRangeToCsvFile_(fileName); // Create a file in Drive with the given name, the CSV data and MimeType (file type) DriveApp.createFile(fileName, csvFile, MimeType.CSV); } else { Browser.msgBox("Error: Please enter a CSV file name."); } } function convertRangeToCsvFile_(csvFileName) { // Get the selected range in the spreadsheet var ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSelection(); try { var data = ws.getValues(); var csvFile = undefined; // Loop through the data in the range and build a string with the CSV data if (data.length > 1) { var csv = ""; for (var row = 0; row < data.length; row++) { for (var col = 0; col < data[row].length; col++) { if (data[row][col].toString().indexOf(",") != -1) { data[row][col] = "\"" + data[row][col] + "\""; } } // Join each row's columns // Add a carriage return to end of each row, except for the last one if (row < data.length-1) { csv += data[row].join(",") + "\r\n"; } else { csv += data[row]; } } csvFile = csv; } return csvFile; } catch(err) { Logger.log(err); Browser.msgBox(err); } }
- Save your code and reload your spreadsheet. Select the range in your spreadsheet that you want to save as a CSV file. Select CSV and then Save as CSV file. Enter the name for the file.
The file will be created and will show up in your Google Drive.