3

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?

1

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];
}
1

It can't be done using the Google Drive search feature but it can be scripted using the Google Sheets API.

First you need to retrieve the list of spreadsheets that you have access to. Next, you loop over those worksheet IDs and perform a search within the rows, looking for the "$1000" value or anything else you want to find. If there is a matching row then you want to keep a copy of its ID and likely the parent spreadsheet ID. At the end you'll have a list of sheets that contain your search query.

This is probably not the answer you were hoping for. I'll see if I can dig up some existing code that someone put together for this exact task.

Update: if you want to code a solution that uses the API, check out this simple Python library. The test files have examples of how one would get all their spreadsheets and search the sheets for some value. I would type out some code but I'm on vacation with just my phone.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.