Extract comma delineated data in cell to template

[Har]

Google Sheets will calculate the number of miles between addresses which is needed for my dispatch and management program. It is handy as there is an autofill function in GoogleSheets/Appsheet and (I assume) needed format for Google Maps which calculates the (also needed) distances. But, it is in a comma delineated format within a single cell.

From the parent table, I would like to prepare a series of letters & emails using table data. To format the data, I need to be able to take the comma delineated data within the address cell and extract to separate lines, address, city, and state/zip. 

Where could I find instructions on the template formatting that will allow me to extract from a cell to multiple document or template lines?

Google Sheet:   2010 Sandberg, Austin, TX, 78767
           
Template Doc: 2010 Sandberg
                            Austin, TX 78767

[Bethel_Vasto · Accepted solution]

If I were to do this for my own app, I would choose to use SPLIT() and INDEX() in virtual columns on the table and then only use column name variables in my template.

[Har]

Thanks, but I'm a newb and unfamiliar with SPLIT() and INDEX(). Did a quick search and found examples in straight programming which I could apply (rudimentary experience in javascript), but still unsure how to do in appsheet or google sheets. Create a script in Google Sheets Apps Script?

[Bethel_Vasto]

You do not need to create a Google Apps Script. All of this can be done in Appsheet 🙂
I would start by getting familiar with Virtual Columns , then learn how to SPLIT() (in your case the documentation has exactly what you need, which is SPLIT("Banana, Apricot, Grapes", ", ") produces a three-item list: Banana, Apricot, Grapes.)

At this point you would need to create one virtual column for each one of the values that you have extracted , and you would need to learn how to use INDEX() in order to achieve this.

Once you've done this, you can head over to the automation pane and create an event that triggers the bot for the pdf creation. If you don't know how to create templates you can start by reading the documentation but I also recommend trying to have appsheet create a template for you and tweaking it to your needs.
You can do this by going to Processes, select "Run a task", then on the right hand side sidebar choose "create a new file", "pdf", and under Template click "create", Then "view".

[Har]

Sorry to take so long to accept as a solution. First, I wanted to implement and then see if I could create a document from a template using that solution. For others reading this string, in the formula column, I used sequential numbers for column deleniated data:
INDEX(LIST([OriginAddress]),1)

[SkrOYC]

---

@Har wrote:

Google Sheet:   2010 Sandberg, Austin, TX, 78767

---

It's always going to look like this? 3 commas?

[Har]

I'm not sure. . .

The cells are auto-filled from Google Maps; so I'm assuming there could be instances where the address is two lines. Also, I've made that an editable field in the event an apartment number or the like is required, but added manually. Perhaps a better solution would be to create a separate column for Appartment or supplement address.

I'm starting now to try the SPLIT() and INDEX() formatting in appsheet. Will post again if I can (or can't) make it work.

[SkrOYC]

---

@Har wrote:

Perhaps a better solution would be to create a separate column for Appartment or supplement address.

---

💯

And if you create a table with all the options, you could limit it with a dropdown

[Har]

Good suggestion, as Google Maps doesn't use apartment numbers and I haven't found where adresses have multiple comma deleniated items. Thanks for the input.

[Steve]

---

@Har wrote:

INDEX(LIST([OriginAddress]),1)

---

This can be written simply as [OriginAddress]. The use of LIST() and INDEX() are entirely unnecessary.

[Har]

As it turns out, I'm still having problem having my template pick up data from my virtual column, so I'd like to use [OriginAddress], if possible. My problem is that the address is comma delineated in one cell so that it can be used by Google Maps for calculating distance. Also, it will auto complete when entering. So, when I try to create a letter type document with street on one line and city and state on another, I'm stuck. . . .

[Steve]

AppSheet has no means to interpret a full address and pick out the individual components. Addresses are notoriously inconsistent in format, so anything you cobble together yourself will fail frequently. Your best bet is to collect the components (e.g., street, city, state) individually rather than as a single address that includes all of it.