Google-sheets – Search for a value in all Google Sheets at once

google sheetsgoogle-drive-search

I have hundreds of spreadsheets in Google Drive and I am trying to find a value in one of the files.

Google drive

For example, I need to find all sheets that have a $1000 value, but I don't want to open each one of my spreadsheets.

Value

How can I search for a value in all of my Google Sheets?

Best Answer

Here is an Apps Script that searches all of current user's spreadsheets. If you have a lot of spreadsheets, it might time out of hit some execution quota, in which case it may be better to restrict it to a folder: replace DriveApp.getFilesByType with

DriveApp.getFolderById('folder id here').getFilesByType

The value to search is entered on var value = line. Keep in mind that dollar sign in $1000 is just an element of formatting; the value is 1000.

The search results (Spreadsheet name and sheet name) are logged: press Ctrl-Enter to view the log after the script is finished.

Technical detail: the try-catch block is there because the sheets without data (chart only sheets) throw an error when one tries to access their DataRange.

function searchSpreadsheets() {
  var value = 314;   // the value to search for
  var files = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
  while (files.hasNext()) {
    var ss = SpreadsheetApp.open(files.next());
    var sheets = ss.getSheets();
    for (var i = 0; i < sheets.length; i++) {
      try {
        var values = flatten(sheets[i].getDataRange().getValues());
      }
      catch(e) {
        continue;
      }
      if (values.indexOf(value) != -1) {
        Logger.log('Spreadsheet ' + ss.getName() + ', sheet ' + sheets[i].getName()); 
      }
    }
  }
}

function flatten(arg) {
  return arg.reduce ? arg.reduce(function(a, b) {return a.concat(b);}) : [arg];
}
Related Topic