form -> sheet, date triggered email sender, Firebase updater

form-sheet-firebase-updater.gs

/**
 * A special function that runs when the spreadsheet is open, used to add a
 * custom menu to the spreadsheet.
 */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: '📧 Send pending unlocks...', functionName: 'sendEmailAndUnlockNextTest'}
  ];
  spreadsheet.addMenu('🔬 Baylor', menuItems);
}


function formatPhoneNumber( phoneNumber ) {
  return String( phoneNumber ).replace( /\D/g, '' );
}

function hasValidPhoneNumber( phoneNumber ) {
  var formattedPhoneNumber = formatPhoneNumber( phoneNumber );
  
  return ( formattedPhoneNumber.length === 10 );
}


function sendSms( phoneNumber, body ) {
  var phoneNumber = '+1' + formatPhoneNumber( phoneNumber );
  
  var payload = {
    "to": phoneNumber,
    "body": body
  };
  
  var options =
      {
        "method" : "post",
        'contentType': 'application/json',
        "payload" : JSON.stringify( payload )
      };
   
  UrlFetchApp.fetch( "https://calm-harbor-86356.herokuapp.com/send-sms", options );
}


function setContactInfoProvided( user ) {
  if ( !user )  return;
  
  var payload = '{ "contactInfoProvided": true }';
  var options =
      {
        "method" : "put",
        "payload" : payload
      };
   
  UrlFetchApp.fetch( "https://baylor-44769.firebaseio.com/users/" + user + ".json", options );
}


function formSubmitReply( e ) {
  if ( e === undefined )  return;
  
  // email is 2nd column on form
  var userEmail = e.values[ 1 ];
  
  // phone number is 4th column on form
  var phoneNumber = e.values[ 3 ];
  
  // set contactInfoProvided = true in Firebase
  var username = e.values[ 2 ];
  setContactInfoProvided( username );
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  // Set the unlock_at date to 3 months from now
  var currentDate = new Date().getTime();
  var day = 86400000;
  var threeMonthsFromNow = currentDate + (day * 90);
  
  sheet.getRange( lastRow, getColIndexByName( "test2_unlock_at" ) ).setValue( threeMonthsFromNow );
  
  var welcomeMessageSubjectLine = "Welcome to Chemo BrainLAB";
  var welcomeMessage = "We will use this email address to let you know when future tests are available for you.";
  
  // send welcome email
  MailApp.sendEmail(userEmail,
                    welcomeMessageSubjectLine,
                    welcomeMessage);

  if ( hasValidPhoneNumber( phoneNumber ) )  sendSms( phoneNumber, [ welcomeMessageSubjectLine, welcomeMessage.replace( 'email address', 'phone number' ) ].join( '. ' ) );
  
  // schedule follow-up 3 months from now
  //   this should send a reminder email
  //   and unlock next test in Firebase
  // scheduleEmailSender( threeMonthsFromNow );
}​


// unlock next test in Firebase
//   by setting current test index to -1, the next test will be unlocked in the patient's UI
function unlockNextTest( user ) {

  if ( !user )  return;

  var payload = '{ "currentTestIdx": -1 }';
  var options =
      {
        "method" : "put",
        "payload" : payload
      };
   
  UrlFetchApp.fetch( "https://baylor-44769.firebaseio.com/users/" + user + "/currentTestSuite.json", options );
   
}


function sendEmailAndUnlockNextTest() {
  
  // search for users where test2_unlock_at / test3_unlock_at is scheduled for today
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var json = JSON.stringify( range.getValues() );  
  var parsed = JSON.parse( json );
  var keys = parsed.shift();
  
  Logger.log( 'parsed:' );
  Logger.log( parsed );
  
  var USERS_TO_EMAIL = [];

  parsed.forEach(function( rowAsArray, rowIdx ) {

    keys.forEach(function( key, idx ) {

      if ( key === 'test2_unlock_at' || key === 'test3_unlock_at' ) {
        
        var unlockAt = rowAsArray[ idx ];
        
        var currentDate = new Date().getTime();
        
        // find email sent column
        var emailSentColumnName = key.replace( 'unlock_at', 'email_sent' );
        var emailSentKeyIdx = keys.indexOf( emailSentColumnName );
        var emailSentColumnIdx = getColIndexByName( emailSentColumnName );
        
        var emailSentAt = rowAsArray[ emailSentKeyIdx ];
          
        // ensure unlockAt date exists, and that email has not already been sent
        if ( typeof unlockAt == 'number' && currentDate >= unlockAt && typeof emailSentAt != 'number' ) {
                      
          Logger.log( 'currentDate is past ' + key + ' date' );
          Logger.log( currentDate + ' >= ' + unlockAt );
            
          var email = rowAsArray[ 1 ];
          var username = rowAsArray[ 2 ];
          var phoneNumber = rowAsArray[ 3 ];
          Logger.log( 'phoneNumber: ' + phoneNumber );
          USERS_TO_EMAIL.push( [ email, username, phoneNumber ] );
          
          // rowToUpdateIdx is rowIdx + 2 (row starts at 1, and row 1 is column names)
          var rowToUpdateIdx = rowIdx + 2;
          
          // update email_sent column in spreadsheet
          sheet.getRange( rowToUpdateIdx, emailSentColumnIdx ).setValue( currentDate );
          
          // if this is test2, set test3_unlock_at date
          if ( key === 'test2_unlock_at' ) {
            
            // set test3_unlock_at to 9 months from now (+270 days)
            var currentDate = new Date().getTime();
            var day = 86400000;
            var nineMonthsFromNow = currentDate + (day * 270);
            
            sheet.getRange( rowToUpdateIdx, getColIndexByName( "test3_unlock_at" ) ).setValue( nineMonthsFromNow );

          }       

        }
        
      }
      
    });
    
  });
  
  Logger.log( 'USERS_TO_EMAIL:' );
  Logger.log( USERS_TO_EMAIL );
  
  // for each user 
  USERS_TO_EMAIL.forEach(function( emailUsernamePhoneNumber ) {
    
    var userEmail = emailUsernamePhoneNumber[ 0 ];
    var username = emailUsernamePhoneNumber[ 1 ];
    var phoneNumber = emailUsernamePhoneNumber[ 2 ];
    
    var nextTestSubjectLine = "Chemo BrainLAB - next test unlocked";
    var nextTestMessage = "Please sign in at: https://chemobrainlab.com.";
    
    MailApp.sendEmail(userEmail,
                      nextTestSubjectLine,
                      nextTestMessage);

    if ( hasValidPhoneNumber( phoneNumber ) )  sendSms( phoneNumber, [ nextTestSubjectLine, nextTestMessage ].join( '. ' ) );
  
    unlockNextTest( username );
  });

}


// send email and unlock next test on date provided
function scheduleEmailSender( date ) {
  
 var triggerDay = new Date( date );
  
 ScriptApp.newTrigger( "sendEmailAndUnlockNextTest" )
   .timeBased()
   .at( triggerDay )
   .create();

}


// helper functions

function getColIndexByName( colName ) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numColumns = sheet.getLastColumn();
  var row = sheet.getRange( 1, 1, 1, numColumns ).getValues();
  for (i in row[ 0 ]) {
    var name = row[ 0 ][ i ];
    if ( name == colName ) {
      return parseInt( i ) + 1;
    }
  }
  return -1;
}