Holden's GAS

GS_for_Holden.gs

// The route to Holden.
var holden_route = 'https://yourdomain/api/_incoming'
// The URL location of the source workbook, as a string.
var source_wb_url = 'https://docs.google.com/spreadsheets/...'
// The SheetID value of the sheet within the source workbook that holds
// additional execution information, as a string.
var exec_sheet_id = '123456778'
// For testing purposes, an integer <= that represents the percentage of each workbook
// you wish to consume. 1 == 100%.
var hunger_level = 1

function getSources(){
  // Get identified source workbook which lists the Google Sheets itended 
  // to serve as data sources.
  var wb = SpreadsheetApp.openByUrl(source_wb_url)
  // Get list of Google Sheets for parsing.
  var source_sheet = wb.getSheets()[0]
  var rows = []
  for (i=2; i<source_sheet.getLastRow()+1; i++) {
    var row = source_sheet.getRange(i, 1, 1, source_sheet.getLastColumn()).getDisplayValues()
    if (row[0][0].length > 0) {
      rows.push(row)
    }
  }
  // Get execution data from the source workbook, including any additional 
  // headers for the request.
  for (q=0; q<wb.getSheets().length; q++){
    if (wb.getSheets()[q].getSheetId() == exec_sheet_id){
      var kwargs = parseWorkbook(wb.getUrl(), exec_sheet_id, null)
    }
  }
  // Check URL protocol for HTTPS, parse data in source Google Sheets, 
  // and make POST requests to Holden for each.
  for (ii=0; ii<rows.length; ii++){
    if (rows[ii][0][1].slice(0, 8) == 'https://')  {
      var url = rows[ii][0][1]
      var sheet_id = parseInt(rows[ii][0][2])
      var row_limit = parseInt(rows[ii][0][3])
      data = parseWorkbook(url, sheet_id, row_limit)
      if (data){
        postRequest(
          data=data,
          assigned_name=rows[ii][0][0],
          primary_key=rows[ii][0][4],
          primary_key_type=rows[ii][0][5],
          header_kwargs=kwargs
        )
      delete data
      }
    } else {
      return Logger.log('Invalid URL format.')
    }
  }
}

function parseWorkbook(url, sheet_id, row_limit){
  // Assumption is the data is contained in rows >=2, field (keys) is contained in row 1.
  // A row limit is supplied to artifically shorten the sheet by skipping any rows that
  // occur before the row identified in in `row_limit`.
  if(!row_limit){
    row_limit = 2
  }
  // Get the Google Sheet workbook supplied in the parameter.
  var wb = SpreadsheetApp.openByUrl(url)
  // Get the specific sheet within the workbook via ID supplied in the parameter.
  for (p=0; p<wb.getSheets().length; p++){
    if (wb.getSheets()[p].getSheetId() == sheet_id) {
      var sheet = wb.getSheets()[p]
    }
  }
  // Parse data on selected sheet by taking the value of each cell and setting it
  // as the value part of a key:value pair where the key is the row 1 value in the
  // same column. If no value in row 1, set key to `unknown`.
  if (sheet){
    var header = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()
    var data = []
    var meal_size = parseInt(Math.ceil((sheet.getLastRow() * hunger_level) + 1))
    for (y=row_limit; y<meal_size; y++){
      var row = sheet.getRange(y, 1, 1, sheet.getLastColumn()).getValues()
      var clean_row = {} 
      for (x=0; x<row[0].length; x++){
        var key = header[0][x]
        if (key.toString().length < 1){
          key = 'unknown' 
        }
        var value = row[0][x]
        clean_row[key] = value
      }
      data.push(clean_row)
    }
    return data
  }
}

function postRequest(data, assigned_name, primary_key, primary_key_type, header_kwargs) {
  // Create base set of headers required by Holden for data management.
  var headers = {
    'Assigned-Name': assigned_name, 
    'Primary-Key': primary_key,
    'Primary-Key-Type': primary_key_type,
  }
  // Add any additional headers specified in the source workbook, such as the Holden key.
  for (k=0; k<header_kwargs.length; k++){
    for(kk=0; kk<Object.keys(header_kwargs[k]).length; kk++){
      key = Object.keys(header_kwargs[k])[kk]
      headers[key] = header_kwargs[k][key]
    }
  }
  // JSON-ify and string-ify the data from a Google sheet and post it to Holden
  var options = {
    'method': 'post',   
    'contentType': 'application/json',
    'payload': JSON.stringify(data),
    'headers': headers,
  }
  var response = UrlFetchApp.fetch(holden_route, options)
  Logger.log(response)
}

To use you will need:

• To copy this script into the Google Apps Script developer console;
• Set the variables in lines 1 - 10;
• Identify at least one Google Sheets workbook that you want to make programmatically available via Holden in the following pattern (keys in row 1, records in rows >= 2):

• Create a new workbook to hold source and execution information that will be consumed by the script;
• Create a "source sheet" within this workbook that lists the above Google Sheets workbook(s) in the following pattern:¹

• Create an "execution sheet" within the same workbook as your "source sheet" that holds the access key that Holden will take from the script's request header and use for validation, as well as any other headers you would like to include in the following pattern:

• Start a Holden with a route that Google servers can access (i.e. not running locally); and
• Run the getSources() function within the script. This will deliver to Holden the data contained in the Google Sheets workbooks you identified on the source sheet (along with identifying information supplied) and include the header information in the execution sheet.

---

1 Note that columns D, E, and F are totally optional and are intended to make data matching easier for applications consuming Holden data.