Can I pick a cell from every Google Sheet in a Folder?

Question

I want to be able to pick say C3 from a list of Google spreadsheets in a folder.

I have a bunch of structurally identical sheets, but I'd like to be able to provide a sum of the values in C3 across say a hundred sheets in a directory.

Ultimately, would be great to highlight the largest or smallest value of C3 in a directory.

This could be useful in many places where you want to be able to aggregate, aggregate data.

Answer 1

SUGGESTION

If you have hundreds of Google spreadsheet files in a Google Drive folder, I agree with @player0 that it is best to use a script. With the Apps Script, you can:

1. Automate the process in iterating through Spreadsheet files in your Drive folder.
2. Filter only the Google Spreadsheet type (e.g you have a bunch of different file types inside).
3. Get the range data & process them the way you want.

See this sample below that was derived from existing resources:

Script:

function readSheetsInAFolder() {
  //FOLDER_ID is your drive folder ID
  var query = '"FOLDER_ID" in parents and trashed = false and ' +
    'mimeType = "application/vnd.google-apps.spreadsheet"';
  var range = "C3"; //The range to look for on every Spreadsheet files in the Drive folder
  var files, pageToken;
  var finalRes = [];

  do {
    files = Drive.Files.list({
      q: query,
      maxResults: 100,
      pageToken: pageToken
    });
    files.items.forEach(sheet => {
      finalRes.push(viewRangeValue(range, sheet.id));
    })
    pageToken = files.nextPageToken;
  } while (pageToken);
  const arrSum = array =>
    array.reduce(
        (sum, num) => sum + (Array.isArray(num) ? arrSum(num) : num * 1),
        0
    );

    var max = Math.max.apply(null, finalRes.map(function(row){ return Math.max.apply(Math, row) })); //Gets the largest number
    var min = Math.min.apply(null, finalRes.map(function(row){ return Math.min.apply(Math, row); })); //Gets the smallest number
    var sum = arrSum(finalRes) // Gets the sum
    console.log('RANGE VALUES: %s \nRANGE: %s \nTOTAL SHEET(s) FOUND: %s \n________________\nSUM OF VALUES: %s \nLargest Value: %s \nSmallest Value: %s',finalRes,range, files.items.length,sum,max,min)
}

function viewRangeValue(range, sheetID) {
  var sid = sheetID;
  var rn = range;
  var parms = { valueRenderOption: 'UNFORMATTED_VALUE', dateTimeRenderOption: 'SERIAL_NUMBER' };
  var res = Sheets.Spreadsheets.Values.get(sid, rn, parms);
  return res.values.map(num => {return parseInt(num)});
}

Demonstration:

• Sample Test Drive Folder (w/ 3 test Spreadsheet files):

Every C3 cell on each of these 3 files contain either 0,10 or 6 value.

• On the Apps Script Editor, I've added the Drive & Sheets API on the services:

Result

After running the script:

Resources:

• Advanced Drive Service
• Drive API Files: list
• Sheets API spreadsheets.values.get
• Max Value of an array