simple_event_registration_form.md

Simple event registration with Google Forms and registration number assignment

• create new gmail account

• create new form forms.google.com

  • set settings responses collect email addresses
  • fill general event info to main/first form element
  • add name (Jméno), surname (Příjmení) fields (i18n names hardcoded in script)
  • add checkbox for permissions to process PI data
  • note the link taken directly from addressbar includes "request to access" button. get published link by form send link.

• add registration ID generator and mail notification

  • view reponses in sheet
  • insert column name "RegistrationId" as first column
  • add new sheet "counter"
  • create registration confirmation function in sheets menu extensions app script
    • create new project, name by event
    • add Code.gs
    • add checkQuota.gs (free account daily quota 100 mails)
    • save only, project does not need to be "deployed"
    • trigger Run to setup OAuth permissions to script "lambda"
    • create function trigger "From spreadsheet - On form submit" on "mySendRegistrationResponse" function
    • test and monitor with app script execution logs

• create document with detailed event info

• create free bit.ly account via google federation

  • create named links
    • registration form link (via form send link, not from address bar)
    • detailed info link (fill back link to script)
  • note that free account cannot update links after creation !

Code.gs

function mySendRegistrationResponse(e) {
  // create registrationId from counter
  var counterLock = LockService.getScriptLock();
  if (!counterLock.tryLock(10000)) {
    throw "error accessing registration counter";
  }
  var counterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('counter');
  var counterRange = counterSheet.getRange(1, 1);
  var counterValue = counterRange.getValue();
  if (!counterValue) {
    counterRange.setValue(1);
  } else {
    counterRange.setValue(counterValue+1);
  }
  var registrationId = counterRange.getValue();
  counterLock.releaseLock();

  // assign registration id to current record
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var activeRow = activeSheet.getActiveSelection().getRowIndex();
  regidRange = activeSheet.getRange(activeRow, 1);
  regidRange.setValue(registrationId);

  // send confirmation email
  var givenname = e.namedValues["Jméno"][0];
  var surname = e.namedValues["Příjmení"][0];
  var email = e.namedValues["Email Address"][0];
  var emailSubject = "Registrace na XXXX";
  var emailBody = `
Vážený pane/paní,<br/>
<br/>
děkujeme Vám za registraci na XXX.<br/>
<br/>
Jméno a příjmení: <b>${givenname} ${surname}</b><br/>
E-mail: <b>${email}</b><br/>
Číslo: <b>${registrationId}</b><br/>
<br/>
Podrobné informace naleznete na stránce
<a href="https://bit.ly/XXX">https://bit.ly/XXX</a><br/>
<br/>
S pozdravem<br/>
Organizační tým<br/>`;

    MailApp.sendEmail({
      to: email,
      cc: "XXX@gmail.com",
      subject: emailSubject,
      htmlBody: emailBody
    });
}

checkQuota.gs

function checkQuota() {
  console.log(MailApp.getRemainingDailyQuota());
}