Search code examples
regexgoogle-apps-scriptgoogle-sheetsregex-replace

Google App Script - Web App - Not saving forum data in Google Sheets in the correct format

Here is a Google App Script Form that I created to save submissions into a Google Sheet.

Code.gs

function doGet() {
  var template = HtmlService.createTemplateFromFile('Page');
  return template.evaluate();
}

function processForm(form) {
  var ss = SpreadsheetApp.openById('SPREADSHEET_ID');
  var sheet = ss.getSheetByName('Submissions');
  var ref = form.reference;
  var agentIds = form.agentIds.split(/[\s,]+/g).map(f => f.trim().replace(/[A-Z]/ig, "")).join(",");
  


  var values = sheet.getRange('B:B').getValues().flat();
  
  if (values.includes(ref)) {
    return "This reference number has already been used for a previous roster submission";
  } else {
    sheet.appendRow([new Date(), ref, agentIds]);
    return "Thanks! Your roster has been submitted successfully";
  }
}

Page.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form id="myForm">
      <label for="reference">Class Reference #</label>
      <input type="text" name="reference" id="reference"><br><br>
      <label for="agentIds">Agent IDs:</label>
      <textarea name="agentIds" id="agentIds" rows="10"></textarea><br><br>
      <input type="button" value="Submit" onclick="submitForm()">
    </form>
    <div id="result"></div>
    <script>
      function submitForm() {
        var form = document.getElementById("myForm");
        google.script.run.withSuccessHandler(showResult).processForm(form);
      }
      
      function showResult(result) {
        document.getElementById("result").innerHTML = result;
      }
    </script>
  </body>
</html>

HOW IT WORKS

The form consists of 2 fields.

  • Class Reference # (A unique number assigned to each class)
  • Agent/Employee ID's Textarea

The users may use the following formats when entering the Agent ID's into the textarea, including typos such as extra spaces, commas, or a mixture of all 3 formats.

A) Separated by a space.

A123456 B001234 TMP00123456

B) Separated by a comma (with or without a space too)

A123456,B001234, TMP00123456

C) One / line.

A123456
B001234
TMP00123456

THE PROBLEM

The Agent ID's are being saved as 12,345,600,123,400,100,000

The expected results should be 123456,001234,00123456

When the form is submitted, the following occurs...

  1. REGEX will clean the Agent ID data so it has the output value of 123456,001234,00123456 - This part is broken.
  2. The script will check to see if the class reference has already been submitted. IF not, it will continue.
  3. The timestamp, Reference, and Agent ID's are now added to the Google Sheet.
Timestamp Reference # Agent IDs'
3/1/2023 0:04:12 Class105 12,345,600,123,400,100,000

Thanks for your help!

Solution

  • I think that the reason for your current issue is that the value is automatically formatted as a number value. In this case, how about the following 2 patterns?

    Pattern 1:

    In this pattern, the number format of the cell for putting agentIds is changed as text.

    From:

    sheet.appendRow([new Date(), ref, agentIds]);

    To:

    var row = sheet.getLastRow() + 1;
sheet.getRange(row, 3).setNumberFormat("@");
sheet.getRange(row, 1, 1, 3).setValues([[new Date(), ref, agentIds]]);

    Pattern 2:

    In this pattern, a single quote is added to the top of the text.

    From:

    var agentIds = form.agentIds.split(/[\s,]+/g).map(f => f.trim().replace(/[A-Z]/ig, "")).join(",");

    To:

    var agentIds = "'" + form.agentIds.split(/[\s,]+/g).map(f => f.trim().replace(/[A-Z]/ig, "")).join(",");

    Note: