Fetch values from multiple sheets Google sheets

Question

I'm trying to find an Employee ID from the attendance which spans multiple sheets and pull the timestamp into a single sheet.

The Google sheet has multiple sheets in it. There are individual sheets for every working day: Daily sheets

Each attendance sheet has two columns. In order to know all the times of the employee's login I want to pull in all the occurrences of the employee ID from all the sheets and along with its timestamp to the Consolidation sheet: .

I've done a similar thing in Excel and guess can be done in Google sheet using Google Apps script.

It would be helpful if someone can guide me to a built-in or custom function in google sheets.

I guess you will have to write a custom function for that. If the total amount of data is small enough to fit into the available ram you can just read all the information into javascript objects/arrays and then write it out in the right format to the consolidation sheet. — SpiderPig
@SpiderPig Custom sheet functions cannot utilize any services that require authentication as they are executed as an anonymous user, such as the SpreadsheetApp service. See this for more details: developers.google.com/apps-script/guides/services/authorization — Douglas Gaskell

Joshua Dawson Joshua Dawson · Accepted Answer · 2016-07-30T23:21:44

I'll help you with a basic outline, some advice, and some resources to help, but don't expect me to write the whole thing.

Step 1 - Create a custom menu option

You'll want to be able to access you script from the spreadsheet. You can accomplish this by creating a custom menu option. Here's an article by google on custom menus.

Although google uses the simple trigger onOpen, I've found installable triggers to be more reliable.

Step 2 - Get user input

It would be nice to be prompted for the id of the employee and have the script work it's magic. Here is an article by google on dialogs and sidebars that discusses how to get user input for a script.

Step 3 - Read and write data from the spreadsheet

You can access spreadsheeet data with the SpreadsheetApp. When calling your script from a custom menu, you can use SpreadsheetApp.getActiveSpreadsheet; however, if you want to test your code in the script editor, there is no "active spreadsheet", so use SpreadsheetApp.openById instead.

With access to the spreadsheet, you can call:

spreadsheet.getSheetByName("name").getRange(1, 1, 2, 2).getValues();
spreadsheet.getSheetByName("name").getRange(1, 1, 2, 2).setValues([[1, 2], [3, 4]]);

Note that getValues and setValues work with 2-dimensional arrays

WARNING - As usual, I/O takes a lot of processing time so avoid superfluously calling getRange().XetValues, this google article about appscript best practices goes into more detail. Also, if you have a LOT of attendance sheets, then you may want to consider condensing them into one.

Step 4 - Get the appropriate sheets

You'll need someway to distinguish which sheets in the spreadsheet are attendance sheets:

function isAttendanceSheet(sheet) {
  return /Magical regex/.test(sheet.getName);
}

var sheets = spreadsheet.getSheets().filter(isAttendanceSheet);

Come up with the magical regex that works for you, and this will filter them.

Hope this helps and good luck!

Fetch values from multiple sheets Google sheets

1 Answers