Split csv string into an Array

Questions about Thunkable
, ,
1

Hi

I am busy with an app in Thunkable X that gets data from google sheets. I parse a URL link to the Web_Api which is a csv export from Google sheets, this is how the URL look,

https://docs.google.com/spreadsheets/d/13iBznZ9icsud8dj2qdukQ8A63VZRvivhTouM9wdd0IE/export?format=csv

image

When i get the data from the URL it stores the data as a string seperated by “,”. And i keep getting a string back like this,

Name,Surname,Username,Email,Password Jason,Kenedy,jasons,jasonk@gmail.com,test1 Bruno,Samuels,brunos,test@gmail.com,test

But I would like to read the data in as an Array with rows and columns, not just a row as currently. My goal is to get the email for a specific Username. I used the “Make list from text” function and seperate with “,” but it then creates a list with all the cells separately and i find it difficult to get the email for a specified username.

This is what i currently have with my blocks, but what can i add to in order to load all the data as an array as i can see it in Google sheets and loop through the records and columns,

image

In classic Thnkable you can use something like this but not in Thunkable x

image

2

Here is an example app which shows how to first get the sheet into a list of rows of column-separated values and then how to extract individual cells/columns from those rows.

Note that there is a bit of a trick in that code. The text of the csv file contains carriage return (aka “cr”) and newline (aka “nl”) characters separating each row of the sheet. Unfortunately, there is not currently a way to easily get those characters into a Text block in Thunkable in order to use them as delimeters. So I added a fake, empty, row at the top of my spreadsheet. Since I know the number of columns in my spreadsheet, I know how many commas will be at the start of the csv file. I can then use that knowledge to extract the carriage return and newline that are at the end of that first line. I then ignore that first line in the rest of the blocks.

I hope that this helps.

-Mark

2 Likes