What is the best way to query from multiple Google Sheets in Google Scripts: index columns

Question

Background:

I am creating a Google Script app which is used for approving/rejecting applications. I have three sheets which reference each other, like a database with foreign keys.

Sheets:

Application

(primary key)
appid           |   userid  |   startdate   |   submitteddate   |   firstname   |   lastname    | ...

This sheet has one row per applicant/userid _{(not all users will have applications)}

Approvals

(primary key)       (foreign key Application)    (foreign key Admin)
approvalid      |   appid                       |   adminid         |   reviewernotes   |   approvalstatus  |   ...

This sheet has multiple rows per application/appid, different admins will weigh in with their thoughts/notes

Admin

(primary key)
adminid         |   adminemail  |   role

This sheet contains the roles of each admin

I'm going to be checking that an admin has specific roles to access/comment/approve or reject an application. Those comments are stored in the Approvals tab.

Currently, the admin info is setup as soon as they login to the app. There are multiple processes I'd like to do for them several get, add, and edit functions.

The Problem:

I know that the best practice is to limit calls to Google Sheets^[1]. So I'm looking for the best way to access this data. Specifically, to do a call like getApprovalsForAdmin(adminid) which would return an object which combines data from both the Application and Approval tabs.

I know this could be done by:

function getApprovalsForAdmin(adminid)
{
  var approvals = [];
  var ss = SpreadsheetApp.openById (spreadsheet_id)
  var sheet = ss.getSheetByName ("Approvals");
  var values = sheet.getDataRange ().getValues ();
  values.forEach (function (value) {
    if (values [2] == adminid){
      approvals.push (value);
    }
  });

  var return_values = [];
  sheet = ss.getSheetByName ("Application");
  var applications = sheet.getDataRange ().getValues ();
  applications .forEach (function (application){
    approvals.forEach (function (approval){
      if (application [0] == approval [1]){
        return_values.push (createReturnValue (application, approval));
      }
    });
  });

  return (return_values);
}

This, however, does have two separate ss.getSheetByName(...) calls.

Is there a way to do this with only one call?

My Search for and Answer:

First

I have seen people make ONE sheet with all the data merged together, but I'm not sure this is the best solution because of how fluid people submitting either application or comments via approvals, is. A merged sheet would be updated frequently, and might cause more of a synchronization headache.

Second

The closest answer I have found: Using Google Scripts to Query Multiple Google Sheets to A View written back in July 2018.

The only solution given is that the person should use a traditional database. I'm trying to make this quickly, and while the data changes a lot, it isn't going to grow into a huge thing. This sounds like overkill in my situation.

Third

This title looked promising: Fetch values from multiple sheets Google sheets However, the solution involved making serveral calls to spreadsheet.getSheetByName("name") which is exactly what I'm wondering if could be simplified.

==========================

Footnotes

^{[1] https://developers.google.com/apps-script/guides/support/best-practices}

I wouldn't worry about that level of optimization. I'm not sure it can even be done. You only have 3 sheets and you're not even looping through them, so it's not a big deal. Generally, you'd want to limit calls for more "local" actions like calling .getRange().getValue() for each cell in a row. You could, however, look at something like passing ss or sheet between functions to prevent duplicate requests. — Diego
I think that your script occurs an error at var values = sheet.getValues (); and var applications = sheet.getValues ();. But from I know this could be done by: in your question, I thought that this might be a copy and paste mistake. How about this? — Tanaike
@Diego - I'll be doing it a few times over the span of a user interacting with the program. and @Tanaike, you're right, it should be var values = sheet.getDataRange ().getValues (); and var applications = sheet.getDataRange ().getValues (); — KitzyKitt
@KitzyKitt I understand, but there's still not really any optimization that can be done there. You could try using the CacheService, but then you may run into issues with the cache not being fully up-to-date. — Diego
Also, be aware that .getDataRange() will not return values hidden by a filter. — Diego

Rubén Rubén · Accepted Answer · 2019-12-06T05:49:42

getRangeList(A1Notations) could be what you are looking for.

Returns the RangeList collection representing the ranges in the same sheet specified by a non-empty list of A1 notations or R1C1 notations.

Or Method: spreadsheets.values.batchGet but to use it you should use the Sheets Advanced Service

Google Sheets API v4: batchGet not working