1

INTRO

I have a Google App Script Web App which has a form which contains a TextArea, and when submitted saves the data in a Google Sheet.

This text-area collects Employee ID's and will receive input from non-tech savvy users.

The users may use the following formats when entering the 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 OBJECTIVE

I need to cleanse the data above before its written to the Google Sheet. The cleansed (expected outcome) data should look like

123456,001234,00123456

THE PROBLEM

I cant seem to get REGEX to work correctly. I have tried many variations.

var agentIds = form.agentIds.replace(/[^\d]+/g, ',').replace(/^,+|,+$/g, '');

In a nutshell, I am looking for regex to...

  1. Replace all spaces with comma.
  2. Replace all line-breaks with commas.
  3. Remove all letters.
  4. IF there are 2 or more commas next to each other, remove the extras.

II am not sure if this is the most efficient way...but the only way I could think of. I am open to suggestions.

Thanks for your help!!! :)

JamesReed68
  • 399
  • 2
  • 14
  • 1
    I have to apologize for my poor English skill. Unfortunately, I cannot understand the sample input and output values you expect. In your question, `123456,001234,001234567` is your sample input value? If my understanding is correct, what is your expected value? – Tanaike Mar 01 '23 at 07:20
  • Hi @Tanaike sorry for the confusion. I have now updated my question by re-arranging the information. `123456,001234,001234567` is the expected outcome value saved to the google sheet. Formats A,B, or C are the formats before regex is used. – JamesReed68 Mar 01 '23 at 07:29
  • Thank you for replying. From your reply, can I ask you about the detailed logic for retrieving `001234567` from `TMP00123456` of `A123456 B001234 TMP00123456`? In this case, the sample input value is `TMP00123456`. But, `001234567` is retrieved. – Tanaike Mar 01 '23 at 07:33
  • Input contains letters & numbers (letters indicates the business). Output data saved to spreadsheet contains numbers only. Once ID's are in spreadsheet, another script is executed to lookup the ID (without numbers), and search them in a database to find the matching ID (without numbers), and return the employee name, email, and other details. – JamesReed68 Mar 01 '23 at 07:45
  • Although unfortunately, I cannot know about the situation that `001234567` is retrieved from `TMP00123456`, I proposed an answer by guessing that it might have miscopied. Please confirm it. If I misunderstood your question and that was not useful, I apologize. – Tanaike Mar 01 '23 at 07:46
  • I now understand what you mean. This was a typo mistake by me. I have updated the original question to avoid confusion. I'm sorry, and thanks for your help! – JamesReed68 Mar 01 '23 at 07:49
  • Thank you for replying. I understood `a typo mistake`. So, I updated my answer by adding one more sample script for converting the value. Please confirm it. – Tanaike Mar 01 '23 at 07:52

1 Answers1

1

I believe your goal is as follows.

  • You want to retrieve a value of 123456,001234,001234567 from "A123456 B001234 TMP00123456", "A123456,B001234, TMP001234567", "A123456\nB001234\nTMP001234567".

In this case, how about the following sample script? In this sample script, I used split and replace.

Sample script:

const values = ["A123456 B001234 TMP00123456", "A123456,B001234, TMP00123456", "A123456\nB001234\nTMP00123456"];
const res = values.map(e => e.split(/[\s,]+/g).map(f => f.trim().replace(/[A-Z]/ig, "")).join(","));
// or const res = values.map(e => e.replace(/[\s,]+/g, ",").replace(/[A-Z]/ig, ""));
console.log(res);

  • When this script is run, ["123456,001234,00123456","123456,001234,00123456","123456,001234,00123456"] is obtained.

  • In your sample input value and output value, it seems that a value of 001234567 is retrieved from TMP00123456 of A123456 B001234 TMP00123456. I'm worried that you might have miscopied TMP001234567 as TMP00123456. I'm not sure about the detail of this.

  • If you want to use this script for one value, how about the following sample script?

    const sample = "A123456,B001234, TMP00123456";
const res = sample.split(/[\s,]+/g).map(f => f.trim().replace(/[A-Z]/ig, "")).join(",");
// or const res = sample.replace(/[\s,]+/g, ",").replace(/[A-Z]/ig, "");
console.log(res);

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thank you for your detailed reply. I am interested in "If you want to use this script for one value, how about the following sample script?". When I use your script in console, it works perfectly. When I add it to Google App Scripts, it breaks. Input Value: [A123456 B001234 TMP00123456] Output Value: [12,345,600,123,400,100,000] It looks like the issue is not with regex. It looks like something else it wrong? I will mark your answer is correct (because it is) and post a new question. Thanks @Tanaike <3 – JamesReed68 Mar 01 '23 at 07:59
  • 1
    @JamesReed68 Thank you for replying. About `sample.split(/[\s,]+/g).map(f => f.trim().replace(/[A-Z]/ig, "")).join(",")`, in this case, first, the value is split by `[\s,]+`, and the alphabet characters are removed from each element, and the array is converted to a string using `join`. About `sample.replace(/[\s,]+/g, ",").replace(/[A-Z]/ig, "")`, first, `[\s,]+` is converted to `,` from the input value, and the alphabet characters are removed from each element. From your question, I thought that you might also want other patterns. So, I proposed to them. – Tanaike Mar 01 '23 at 08:07
  • @JamesReed68 By the way, I cannot understand the logic for obtaining `[12,345,600,123,400,100,000]` from `[A123456 B001234 TMP00123456]`. I apologize for this. – Tanaike Mar 01 '23 at 08:08
  • I am very confused too! Thanks for your help! If you have an interest in learning about odd Google App Script problems, I just posted my web app source code, and problem as described in this question at: https://stackoverflow.com/questions/75601055/google-app-script-web-app-not-saving-forum-data-in-google-sheets-in-the-corr – JamesReed68 Mar 01 '23 at 08:17