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}