Relate calculated data model to Cloud SQL model

I am needing to create a single table that displays values from a Cloud SQL data model and Calculated data model side by side as if the data models were related.

Costs Table

I have a calculated data model named 'Costs' that is populated from a spreadsheet. The data in this table looks like this:

PROJECT_ID, ACCRUED_COSTS, REMAINING_COSTS
0001      , 10000        , 25000
0002      , 25000        , 2000
0003      , 15000        , 5000

The code I'm using to generate the calculated model is here:

return getSSData();

function getSSData(){
  var values = SpreadsheetApp.openById("SPREADSHEET_ID").getSheetByName("SHEET_NAME").getRange("RANGE").getValues();
  var ssData = [];
  for (var i = 0; i<values.length; i++){
    var newRecord = app.models.costsTable.newRecord();
   // add all fields to the new record
    newRecord.project = values[i][0].toString();
    newRecord.projectID = values[i][1].toString();
    newRecord.actual = values[i][2];
    newRecord.estimate = values[i][3];
    ssData.push(newRecord);
  }
// return the array of the model.newRecord objects that would be consumed by the Model query.
  return ssData;
}

Projects Table

I also have a Cloud SQL data model named 'Projects'. The data in this table looks like this:

PROJECT_ID, PROJECT_NAME   , PROJECT_BUDGET
0001      , project_alpha  , 50000
0002      , project_beta   , 30000
0003      , project_charlie, 19000

Combined Table

I need to create a page containing a table widget that displays all columns of data from both tables side by side like this:

PROJECT_ID, PROJECT_NAME   , PROJECT_BUDGET, ACCRUED_COSTS, REMAINING_COSTS
0001      , project_alpha  , 50000         , 10000        , 25000
0002      , project_beta   , 30000         , 25000        , 2000
0003      , project_charlie, 19000         , 15000        , 5000

The Problem

This would be easy if the tables could be related to each other but unfortunately calculated models do not support relating.

Solution

Per earlier comment you have two options to get this accomplished. Either way add the additional fields like ‘Project_Name’ and ‘Project_Budget’ to your calculated model.

Option 1 (fetch all records with index function):

function getSSData(){
  var values = SpreadsheetApp.openById("SPREADSHEET_ID").getSheetByName("SHEET_NAME").getRange("RANGE").getValues();
  var projects = app.models.Projects.newQuery().run();
  var ids = projects.map(function(p){return p.PROJECT_ID;});
  var ssData = [];
  for (var i = 0; i<values.length; i++){
    var newRecord = app.models.costsTable.newRecord();
   // add all fields to the new record
    newRecord.project = values[i][0].toString();
    newRecord.projectID = values[i][1].toString();
    newRecord.actual = values[i][2];
    newRecord.estimate = values[i][3];
    var index = ids.indexOf(values[i][1].toString());
    if(index !== -1) {
      var project = projects[index];
      newRecord.Project_Name = project.PROJECT_NAME;
      newRecord.Project_Budget = project.PROJECT_Budget;
    }
    ssData.push(newRecord);
  }
// return the array of the model.newRecord objects that would be consumed by the Model query.
  return ssData;
}

Option 2 (run line item query):

function getSSData(){
  var values = SpreadsheetApp.openById("SPREADSHEET_ID").getSheetByName("SHEET_NAME").getRange("RANGE").getValues();
  var ssData = [];
  for (var i = 0; i<values.length; i++){
    var newRecord = app.models.costsTable.newRecord();
    var query = app.models.Projects.newQuery();
    query.filters.PROJECT_ID._equals() = values[i][1].toString();
    var results = query.run();
   // add all fields to the new record
    newRecord.project = values[i][0].toString();
    newRecord.projectID = values[i][1].toString();
    newRecord.actual = values[i][2];
    newRecord.estimate = values[i][3];
    if(results.length > 0) {
      var project = results[0];
      newRecord.Project_Name = project.PROJECT_NAME;
      newRecord.Project_Budget = project.PROJECT_BUDGET;
    }
    ssData.push(newRecord);
  }
// return the array of the model.newRecord objects that would be consumed by the Model query.
  return ssData;
}

I would encourage you to try both. Create a variable for startdate = new Date() at the beginning and then before you return the data do console.log(new Date() - startdate), which will give you the time difference in milliseconds of your total script execution. That way you can also figure out for yourself which process is faster.