Search Google Drive files and extract row data

The following Google Apps Script is designed to search the content of files on Google Drive which includes the given search word(s). Once it has collated these files it will then go into each one, search for the row of data that matches our search word(s) and extract that into a collective Google Sheet.

The information collected from the initial search to collate the files includes:

• The filename,
• The file ID,
• A direct/clickable link to the file,
• The type of file - this tool is restricted to Google Sheet.

The information collected from the second part of the tool includes:

• A direct/clickable link to the file,
• The Header row from the file,
• The row of data from the file.
  

Search and extract the content of Google Drive files

Instructions

Please follow the guidance on the 'Welcome' sheet to provide the required information. Use the 'Admin' menu to run each part of the tool:

1. Search Google Drive - to collate the files.
2. Extract File Contents - run after the above to go through and get the data.
3. Reset sheet - this is to be used if you want to clear all of the data and start from scratch.
   

'getFiles' sheet is where the file information will be collated by the tool. The Content Extracted column is updated after the next step with a 'Yes' and a row will be skipped if it already contains a value in that cell.

The 'extractContent' sheet is where the collected data from each file will be appended. The 'Log' sheet is to help debug any problems and you will receive a popup message to also inform you of any problems the tool encounters.

The Code

There are a couple of elements of code I wanted to highlight that are very new and have been part of the learning with this project. As usual I include comments as much as possible to help you understand/manipulate the code.

The 'DriveApp.searchFiles()' method is the way we are going to go out and search Google Drive for what we want. There are a number of file-specific query terms that we can use depending on what we require, in this instance we want fullText:

var files = DriveApp.searchFiles(
    "fullText contains" + "'" + searchString + "'"
);

Download

Search Google Drive files and extract row data here (please use 'File' > 'Make a copy' for your own version).