Automate converting text to rows and columns (Google Sheets)

Question

I'm trying to convert order form data submitted from a Squarespace website from the following format to a table with 4 columns:

Store,Item,Quantity,Details;Store2,Item2,Quantity2,Details2; (etc...)

Commas separate columns while semi-colons separate rows. All the methods I've tried so far have been successful in splitting the data into the desired form, but the problem occurs when new data is added. When the form is submitted, it creates a new row in the next available empty row. I can't seem to find a way to automate the process without receiving cyclical dependency errors, since each order can have any amount of item entries.

Example spreadsheet: https://docs.google.com/spreadsheets/d/1ZEWtmMiWO0Us76Z7o7GB7Salw1Rl_-1PhK6GzeOD0GM/edit?usp=sharing

The above example splits the data as desired. I cannot figure out how to make it work with the data added as a new row. I would also like to continue using sheets for its cloud functionality.

Any advice is appreciated, including entirely new ways of processing the data, whether with a script, a different remotely accessible order processing app compatible with Squarespace forms, or natively within Sheets.

halfer · Accepted Answer · 2020-05-16 21:32:59Z

2

You want to achieve the following conversion.

Sample formula:

=ARRAYFORMULA(SPLIT(TRANSPOSE(split(A4,";")),","))

In this formula, the cell "A4" has the input value.
You have already used the formula of =TRANSPOSE(split(A10,";")). In this answer, I used this.
- For TRANSPOSE(split(A10,";")), the value is splitted with , using SPLIT and ARRAYFORMULA.

Result:

Sample script:

When you want to use Google Apps Script, you can also use the following script.

function myFunction(value) {
  const values = value.split(";");
  return values.splice(0, values.length - 1).map(e => e.split(",").map(f => isNaN(f) ? f : Number(f)));
}

In this case, please copy and paste the script to the script editor, and put the custom function of =myFunction(A4) to a cell.
- The same result with above formula can be obtained.

References:

edited May 16, 2020 at 21:32

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Apr 14, 2020 at 0:56

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

kjoy Over a year ago

Thanks for the feedback. Your solution works but requires the function/formula to be dragged down in order to work. This is an issue because the data is added as a new row. So what will happen is that when the data is added, it will go below the last row that the formulas are in, and thus there will be no formula applied to the new data. Is there a way to run a script that reprocesses the whole sheet every time it's run, or some other solution like that?

Tanaike Over a year ago

@kjoy Thank you for replying. I apologize for my incomplete answer for your goal. This is due to my poor skill. In order to correctly understand about your goal, I would like to confirm about it. 1. About what will happen is that when the data is added, how is the data added? It's a script? 2. Where cells are the data added? 3. You want to achieve this using Google Apps Script. Is my understanding correct?

kjoy Over a year ago

No worries, thanks for the quick responses. The data is coming from a form on a Squarespace website. Upon form submission, it is added as a completely new row, with one cell containing the data separated by commas for columns and semi-colons for rows. Google Apps Script seems like a solution that would work, but I have a very limited understanding of it.

Tanaike Over a year ago

@kjoy Thank you for replying. About The data is coming from a form on a Squarespace website., is the data put by a script or Google Form?

kjoy Over a year ago

It is a Squarespace form linked to Google Sheets. Transfers the data to Sheets upon form submission. Effectively the same as a Google Form I suppose.

|

Collectives™ on Stack Overflow

Automate converting text to rows and columns (Google Sheets)

1 Answer 1

Sample formula:

Result:

Sample script:

References:

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample formula:

Result:

Sample script:

References:

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related