Newbie: Best way for non-developer hobbyist to store data

Background Info
I’m a hobbyist and a retired developer with an ancient background in programming. I have a pet project (pretty much for my own use) that I’ve been trying to work on for many years but never had the time to progress with it. As a result, my data has stayed in a Google spreadsheet with Google Scripts automating a couple of functions and the rest still being manual. I’ve recently picked it up again and would LOVE to see if Composer Pro would allow me to finally complete it.

I’ve got less than a thousand records in my main table and several small lookup tables (most are under 20 records). Since it’s for my own use, my budget is almost nil. I need it to work on Windows 10 (or web) plus on my Android phone.

Question 1
My main question is about where to most easily store my data so that I can use it within an Appgyver Composer Pro application. The database could either be SQLite or a no-SQL database or spreadsheet-based. I’ve never used a Rest API so I don’t know anything about that, but I could learn.

Question 2
My second question is whether there is anything prewritten that would allow my data to have a column with Markdown in it that would be formatted when displayed. I not, I’d try to develop it on my own and publish it for your community’s use.

Thanks … Jeff

REST APIs are the way to integrate to a backend at the moment, so there’s some learning to be done, but it’s not a very difficult concept. In essence REST just means an endpoint that returns/accepts (typicallly) JSON-format data, and a specific structure to how the different routes and HTTP verbs (i.e. GET/POST/PUT etc) map to actions.

As such, the best backend for your app is really a balance between price/features/ease-of-use etc. We do hear the call for an “official recommendation”, and the plan is to make a few end-to-end examples/tutorials available.

www.sheetsu.com offers a very nice Backend-as-a-Service solution for Google Sheets and their APIs play nicely with the REST API integration in Composer, but it can be a bit steep for just a single hobby project.

Google Sheets also has their own direct API, but it’s very powerful and as such will require a fair bit of wrapping your head around – typically you’d set up your own backend using one of their client libraries that would provide a more simple API. https://developers.google.com/sheets/api/reference/rest

https://restdb.io/ free plan could also work for you, though I haven’t tried the service myself, just noticed another Composer user recommending it.

The Rich text component renders Markdown, so you would be able to use that!

2 Likes

Thanks for the reply! I might also try using PouchDB

Yup, let us know how it goes – Airtable is also a popular option, though geared more towards project/workflow management; I made a tutorial on how to integrate their API at https://docs.appgyver.com/data/rest-api-direct-integration/integrating-with-airtable

Thanks for this post and article…unfortunately, I’m having the same difficulty making use of a very simple test spreadsheet I made in Airtable:

I set up a data source and a data variable in composer pro and it tests fine (thanks to your article recommending the use of “records” in the response keypath. However, I cannot seem to address anything in the array inside composer:

I’ve tried a whole bunch of ways to get access, they all fail. How can display a value from this matrix, say the value of the cell at the intersection of 1500 and A?

Thanks in advance for your help!

Hi there!

So there’s a couple of things going on here:

  1. First off, as Airtable exports the data through the API it gets converted to an array. Unlike spreadsheets the array doesn’t have “intersections”. Instead, based on your screenshots, it seems each row gets exported as an object with key value pairs like this: {"Groundrun": 1400, "A":6 } (…and every other key/value from the row).

  2. This means that looking for A and 1500 becomes looking for a row which has {"Groundrun": 1500} and then looking at the A key for the value.

This means that instead looking up a value, we’re searching on array for a matching object, which we can do with SELECT

Now we can do
SELECT<row>(data.Coeffmatrix, row.fields.Groundrun == 1500)
to get the row where Groundrun = 1500.

And wanting to access the “A” key the complete formula is:
LOOKUP(SELECT<row>(data.Coeffmatrix, row.fields.Groundrun == 1500) ,"A")

Please check the formulas, as I wrote them without having the data :slight_smile:

Thanks for the assist!