5

A Google survey asked me:

About how many spreadsheets are stored in your Google Drive?

I ended up guessing, because I couldn't find a way to find the real number. In Google Drive interface, there is an option to search for spreadsheets (type:spreadsheet), but the search returns only the 20 most recently active documents. There is no pagination of search result that I could see. So, how can I:

  1. Find how many Google Spreadsheets I have?
  2. (less important) get the list of these spreadsheets?

The Sheets interface theoretically shows all of them, but in the form of infinite scroll, and without a counter.

  • Did you tried to check this on sheets.google.com? – Rubén May 19 '16 at 0:18
  • That interface shows more (answering #2), but as an infinite scroll, so I still don't know how many I have. – user79865 May 19 '16 at 0:19
  • To find out how many spreadsheets could be very challenging as there are no limit for Google spreadsheets by the other side there are several ways to find out this but some of them could not work for all. How many spreadsheets do you think that you have? Are you worried about the Google Apps Script execution time limit? – Rubén May 19 '16 at 1:30
  • No, I'm not worried about that: I just tried to answer the survey question honestly, and was surprised Google didn't make it easy to answer their own question. My guess is that I have a few hundred spreadsheets, but it's just arbitrary. – user79865 May 19 '16 at 1:34
6

The following script will give you the count in the Logger log. 776 in my case. Which was interesting to know.

function findOut() {
  var docs = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
  var i = 0;
  while (docs.hasNext()) {
  var doc = docs.next();
  i++;
 }
  Logger.log(i);
}

This script will list all the names. You could write the data to a spreadsheet as well if you're looking for an index of sorts.

function findOut() {
  var docs = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);
  var i = 0;
  while (docs.hasNext()) {
  var doc = docs.next();
  Logger.log(doc.getName())
 }

}

Your Answer

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