google_app_script.js

// Script to run the LOINC vocab mapper through an OpenFn workflow
// Add menu item
function onOpen(e) {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    .addItem('Run LOINC Vocab Mapper', 'menuItem1')
    .addItem('Reset Workflow URL', 'updateWorkflowUrl')
    .addToUi();
}

function onInstall(e) {
  onOpen(e);
}

// Update OpenFn webhook trigger URL
function updateWorkflowUrl() {
  var ui = SpreadsheetApp.getUi();
  var urlResult = ui.prompt('Workflow URL', 'Enter the new OpenFn workflow trigger URL:', ui.ButtonSet.OK_CANCEL);
  if (urlResult.getSelectedButton() == ui.Button.OK) {
    PropertiesService.getScriptProperties().setProperty('WORKFLOW_URL', urlResult.getResponseText());
  }
}

// Workflow trigger
function menuItem1() {
  var ui = SpreadsheetApp.getUi();
  
  // Try to get the saved workflow URL
  let workflowUrl = PropertiesService.getScriptProperties().getProperty('WORKFLOW_URL');
  
  // If no saved URL exists, prompt the user for it
  if (!workflowUrl) {
    var urlResult = ui.prompt('Workflow URL', 'Enter the OpenFn workflow trigger URL:', ui.ButtonSet.OK_CANCEL);
    if (urlResult.getSelectedButton() != ui.Button.OK) return;
    
    // Save the URL for future use
    workflowUrl = urlResult.getResponseText();
    PropertiesService.getScriptProperties().setProperty('WORKFLOW_URL', workflowUrl);
  }
  
  // Get Spreadsheet information
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const spreadsheetId = ss.getId();

  function getNonEmptyRange() {
    const sheet = ss.getActiveSheet();
    const dataRange = sheet.getDataRange();
    return sheet.getName() + "!" + dataRange.getA1Notation();
  }

  // Format data to send
  const data = { 
    spreadsheetId: spreadsheetId,
    range: getNonEmptyRange(),
  };

  // Trigger the workflow with the input data
  UrlFetchApp.fetch(workflowUrl, {
    method: 'POST',
    contentType: 'application/json',
    payload: JSON.stringify(data)
  });
  // Show toast message
  ss.toast('Check your workflow for progress.', '⏳ OpenFn Workflow started.', 20);
}