Any good tool or tutorial for transforming grouped data into detail?

Hello community!

I am new and have run into a problem when the source google sheet data is not in tabular format. Instead it is grouped into detail type cards instead of rows and columns. For Example each record is 5x5 and there are two columns

First record goes from A1:E5, the second goes from G1:K5
The third A7:E11 and the fourth from G7:K11

Like two rows of detail cards.

Does AppSheet have any tools to make connecting to this data easier? Or google sheets?

My requirements are:

  • the source document must remain in it’s current format,
  • the source data will be frequently changed outside of appsheet,
  • The source data should be read-only to appsheet.

Thanks in advance for any suggestions.

Appsheet requires your data to be in tabular format.

3 Likes

You can easily use Google sheet formula IMPORTRANGE to prepare a sheet for appsheet to read.

Your 4 ranges would be brought into a new spreadsheet that you will use with appsheet.

When the users change the original spreadsheet, the importranged sheet will update it’s data and then a sync from appsheet will read those changes as well.

The only place this will go wrong is if you want appsheet to be able to write changes to the source…but you did say you wanted it to be read only…

Hope that’s given you some pointers to get started…

1 Like

This is great info. Since there are 2 columns of data i was able to import 2 ranges, union them and filter them to display a kind of virtual table. That appsheet was able to use. Thanks.

2 Likes

Sheets is amazing at arrays as well…

You can use the formula

={Sheetname!A1:G7} to pull that array of values into a different area…those values update in real-time as changes are made to the source array.

The curly brackets are important and denote an array.

You could use 2 of those references in the same sheet to create a contiguous set of data in the same way you used IMPORTRANGE on your separate sheet.

There’s always more than one way to get the jobs done :grinning:

1 Like