I’m quite new to thunkable and am looking for advice.
I’m working on a project that would allow a user to record traffic data on a roadway by observation. Whenever a vehicle passes, the user would press the corresponding button (depending what direction the vehicle is travelling), which would record a timestamp in a database. I want to note that the user would record data while offline.
Now the goal is to export the database of timestamps into a spreadsheet (preferably excel, csv, or google sheets) when the traffic observation session is finished. Is there a way to record the timestamps in Local DB and then export those results to a spreadsheet after? Or maybe record the results in google sheets while offline and sync the data when online? I know it’s possible to store the data on google sheets or firebase while online, however, the user would be clicking buttons very frequently for at least a 2 hour period (some sessions could last up to 8 hours of recording traffic), so I feel this method would drain the battery quickly and require the user to have a mobile data plan. Could it be possible to record the timestamps in a list and retrieve that list somewhere in the phone app files?
Any thoughts/ideas would be greatly appreciated and of great help.
You can definitely record timestamps locally on the device and then when the device is back online, upload the data to a cloud-based spreadsheet such as a Google sheet.
You’d probably want to store the data in a “stored” variable list or a local db.
I actually have the timestamps currently being stored as variable list. Do you have an example or can assist me on how I can upload the variable list to a google sheet?
If the device is online… if the list of offline timestamps is empty then add a new row to the spreadsheet with the timestamp as the value; if the list of offline timestamps is not empty then add a row, one by one for each value in the stored list of timestamps; and if the device is offline, simply add a timestamp to the offline list.
I couldn’t get this to work as a test. I think it might have something to do with the create row block just not working. I don’t have a lot of faith in that block.
The wait 1 seconds block is my clumsy attempt to slow down the for each item j loop. If you create rows too quickly in a project, that can fail. There are better methods suggested for handling this on the forums but I don’t have the links to them at the moment.
I used a seconds since 1970 block as an example of a timestamp but you would need to insert your own block/function there.
With @jane’s help, I was able to fix the issue. It turns out that Google Sheets don’t allow uploading of numeric values using the “create row” block. So you have to convert the number to a text string by joining it with an empty string ("") and then use that value in the create row block:
Thank you both @tatiang and @jane, I tried this method and it worked!
I did take another route with the blocks thou; instead of checking if the device is online before collecting timestamps, I simply made the timestamps record into a stored variable list by default, and then all the results get transferred to google sheets via an “Export” button at the end of the session. I tested this using 1 list variable which worked, but now I have to play around and see if this will work for multiple stored list variables at once.
Which leads me to a following question, is it possible to determine which column the stored list variable gets inputted within the google sheet? For example I would have a total of 12 stored list variables at the end of the session, and would need them to be exported to the same google sheet but different column. Is it possible?
The “Timestamp value” spot in the create row block in my screenshot is the column name in my Google Sheet. When you add the sheet as a data source in Thunkable, the create row block automatically has a spot for each column name (provided your columns have headers).
So yes, you can choose to update some or all of the columns in a particular row when you use the create row block.
Forgive me if I’m asking too many questions, but another important question came to mind. Lets say multiple users download the app, collect data, and then export results, would all the results go to the same google sheet which I created initially? Is there a way so that each individual user has the data saved to their own google sheet?
When you connect a Google Sheet to Thunkable, every user of the app sends data to that same sheet. While you could connect a bunch of Google Sheets and code your app so that each new user gets connected to a different sheet, if you have more than a few users or you have an unknown number of users, you probably want to go with Firebase which allows you to dynamically create new user data on the fly rather than relying on an existing database structure as in a spreadsheet.
