Hudson Line Studios - Google Apps Script for waitlist + survey sync

apps-script.js

function doPost(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = JSON.parse(e.postData.contents);
  
  // Ensure headers exist
  var headers = sheet.getRange(1, 1, 1, 9).getValues()[0];
  if (!headers[0]) {
    sheet.getRange(1, 1, 1, 9).setValues([
      ['Timestamp', 'Name', 'Email', 'Phone', 'Type', 'Services', 'Days Available', 'Budget', 'Submission ID']
    ]);
  }
  
  if (data.type === 'survey') {
    // Survey submission — find and update existing row by submissionId or email
    var lastRow = sheet.getLastRow();
    var updated = false;
    
    if (lastRow > 1) {
      var emails = sheet.getRange(2, 3, lastRow - 1, 1).getValues();
      var ids = sheet.getRange(2, 9, lastRow - 1, 1).getValues();
      
      for (var i = emails.length - 1; i >= 0; i--) {
        if ((data.submissionId && ids[i][0] == data.submissionId) || 
            (data.email && emails[i][0] === data.email)) {
          var row = i + 2;
          sheet.getRange(row, 6).setValue((data.services || []).join(', '));
          sheet.getRange(row, 7).setValue((data.daysAvailable || []).join(', '));
          sheet.getRange(row, 8).setValue(data.budget || '');
          updated = true;
          break;
        }
      }
    }
    
    if (!updated) {
      sheet.appendRow([
        data.timestamp || new Date().toISOString(),
        data.name || '', data.email || '', data.phone || '',
        'survey',
        (data.services || []).join(', '),
        (data.daysAvailable || []).join(', '),
        data.budget || '',
        data.submissionId || ''
      ]);
    }
  } else {
    // Waitlist signup — new row
    sheet.appendRow([
      data.timestamp || new Date().toISOString(),
      data.name || '', data.email || '', data.phone || '',
      'waitlist', '', '', '',
      data.submissionId || ''
    ]);
  }
  
  return ContentService.createTextOutput(JSON.stringify({ success: true }))
    .setMimeType(ContentService.MimeType.JSON);
}