Creating a custom regex script in Javascript to properly parse Google Sheets data that contains commas

Question

Creating a custom regex script in Javascript to properly parse Google Sheets data that contains commas

Currently, I am working with a JavaScript script that extracts data from a public Google Sheets feed in a JSON-CSV format that requires parsing.

The rows are separated by commas, but the challenge lies in dealing with unescaped commas within each item. For example:

"a: Feb 21, 10:11, b: some content, c: more, d: even more"

My objective is to achieve the following structure:

{
  "a": "Feb 21, 10:11",
  "b": "some content",
  "c": "more",
  "d": "even more"
 }

Initial Splitting Attempt

Executing data.split(',') results in:

{
  "a: Feb 21,
  "10:11",
  "b some content",
  "c: more",
  "d: even more"
 }

Regex Experimentation

I have tried using the regex pattern: (?[^,]+): (?[^,]+), which almost accomplishes the task of wrapping the comma around a date value at the beginning of the next property occurrence.

Referenced Image - View on Regex101

javascript json regex csv google-sheets

Answer 1

Answer №1

Refer to the UPDATE below for a fresh response.

The given result is not definitive, as a comma might be part of a cell or act as a separator without clear distinction.

If JSON isn't preferred, converting a sheet to .csv format can be done in the following way:

Choose the intended sheet to share, like Sheet2
Select Comma-separated values (.csv) as the format

This action allows access to the CSV data using the URL:

https://docs.google.com/spreadsheets/d/e/<spreadsheet-id>/pub?gid=<tab-id>&single=true&output=csv

An example sheet:

| A1 cell   | B1 |
| A2, content | B2 |

Sample CSV output:

A1 cell,B1
"A2, content",B2

Various tools are handy for parsing CSV files, such as https://github.com/peterthoeny/parse-csv-js

UPDATE, considering Esteban's limitation from altering the data source:

To generate the desired object, utilize split and forEach methods as shown below:

const input = 'a: Feb 21, 10:11, b: some content, c: more, d: even more';
let result = {};
input.split(/, (?=[a-z]+:)/)
.forEach(item => {
  let key = item.replace(/:.*/, '');
  let val = item.replace(/.*?: */, '');
  result[key] = val;
});
console.log(JSON.stringify(result, null, ' '));

Result:

{
 "a": "Feb 21, 10:11",
 "b": "some content",
 "c": "more",
 "d": "even more"
}

Explanation:

let result = {}; - initializes an empty object called result
.split(/, (?=[a-z]+:)/) - splits based on key/value pairs with a lookahead for an alphabet: key
.forEach():
- extracts the key and value from each item
- adds a new property to result object, with the property name being the key

Answer 2

Refer to the UPDATE below for a fresh response.

The given result is not definitive, as a comma might be part of a cell or act as a separator without clear distinction.

If JSON isn't preferred, converting a sheet to .csv format can be done in the following way:

Choose the intended sheet to share, like Sheet2
Select Comma-separated values (.csv) as the format

This action allows access to the CSV data using the URL:

https://docs.google.com/spreadsheets/d/e/<spreadsheet-id>/pub?gid=<tab-id>&single=true&output=csv

An example sheet:

| A1 cell   | B1 |
| A2, content | B2 |

Sample CSV output:

A1 cell,B1
"A2, content",B2

Various tools are handy for parsing CSV files, such as https://github.com/peterthoeny/parse-csv-js

UPDATE, considering Esteban's limitation from altering the data source:

To generate the desired object, utilize split and forEach methods as shown below:

const input = 'a: Feb 21, 10:11, b: some content, c: more, d: even more';
let result = {};
input.split(/, (?=[a-z]+:)/)
.forEach(item => {
  let key = item.replace(/:.*/, '');
  let val = item.replace(/.*?: */, '');
  result[key] = val;
});
console.log(JSON.stringify(result, null, ' '));

Result:

{
 "a": "Feb 21, 10:11",
 "b": "some content",
 "c": "more",
 "d": "even more"
}

Explanation:

let result = {}; - initializes an empty object called result
.split(/, (?=[a-z]+:)/) - splits based on key/value pairs with a lookahead for an alphabet: key
.forEach():
- extracts the key and value from each item
- adds a new property to result object, with the property name being the key

Answer 3

Answer №2

Check out this approach:

(?<tag>\w+): (?<value>.+?)(?:,(?= \w+:)|$)

Demo & breakdown

Answer 4

Check out this approach:

(?<tag>\w+): (?<value>.+?)(?:,(?= \w+:)|$)

Demo & breakdown

Answer 5

Answer №3

If the text in cell A2 is taken as a sample string, you can experiment with this formula:

=SPLIT(REGEXREPLACE(" "&A2,",\s([\w]+[\d]*:)","|$1"),"|")

To explain the REGEX in simple terms: "Locate a comma followed by a space, then some word characters possibly ending with a single digit before another comma. When found, replace the initial comma and space with a pipe symbol while leaving everything else unchanged."

The SPLIT function will be applied to that pipe symbol.

A leading space is included at the start of the entire string so that we can pinpoint a "space followed by a comma" for the first label.

Answer 6

If the text in cell A2 is taken as a sample string, you can experiment with this formula:

=SPLIT(REGEXREPLACE(" "&A2,",\s([\w]+[\d]*:)","|$1"),"|")

To explain the REGEX in simple terms: "Locate a comma followed by a space, then some word characters possibly ending with a single digit before another comma. When found, replace the initial comma and space with a pipe symbol while leaving everything else unchanged."

The SPLIT function will be applied to that pipe symbol.

A leading space is included at the start of the entire string so that we can pinpoint a "space followed by a comma" for the first label.

Answer 7

Answer №4

Give this a shot:

(?[^,\s]+): (?.+:\d\d|[^,]+)

The important thing to remember is to utilize the pipe symbol as an alternative option that first searches for the specific date-time format and then the rest of the content (the initial pattern you were working with).

When testing your regex in the builder on regex101, it should appear like this:

https://i.sstatic.net/FN0Sq.jpg

Answer 8

Give this a shot:

(?[^,\s]+): (?.+:\d\d|[^,]+)

The important thing to remember is to utilize the pipe symbol as an alternative option that first searches for the specific date-time format and then the rest of the content (the initial pattern you were working with).

When testing your regex in the builder on regex101, it should appear like this:

https://i.sstatic.net/FN0Sq.jpg

Creating a custom regex script in Javascript to properly parse Google Sheets data that contains commas

Regex Experimentation

Answer №1

Answer №2

Answer №3

Answer №4

Similar questions

Ways to display the data within a BLOB object

Parsing a JSON object using JSON.NET

Is it possible to invoke this JavaScript function like this?

Optimizing Shader Caching in WebGL: Best Practices

Flask response fails to retain non-visible characters in string

Unlinking styles from the template in Vue.js

Create a new JSON file and add data using ObjectMapper

Tips on extracting information from several JSON response objects

javascript conceal other sections upon hovering

Transform JSON data into JPA entities under the management of Jackson

Is concealing content using Javascript or jQuery worth exploring?

Displaying HTML with extracted message form URL

The JSON object is not providing the length of the array, returning the value as

The JSON object is coming back as null

The Vue ChartJS fails to display properly after the page is refreshed

Retrieving the size of every image with ajax while also storing extra image information

Displaying PHP output within a JavaScript expression

What is the prescribed interface or datatype for symbol type in TypeScript with JavaScript?

Fetching deeply nested data from JSON within Angular Material tables - ANGULAR

What is the best way to differentiate between two calls to the same method that are based on different arguments?