How to extract data based on time to visualize

App Development
1

I would like to extract data from a table in order to visualize it in the application.

Use case:
Different users of the application add new records every day. I would like to visualize in a line chart how many records they have added every day - per user.

Idea:
Automate collection of data, so every day on 23.59 a record should be added to a new table which consists of date, user name, number of records added. That data could be used then in a chart component.

Problem:
I have no idea how to do that, nor did I find something in this community about that.

Can someone point me in the right direction: how can I extract data from tables in order to visualize them in a charts component organized by a time unity.

2

I think that you should wait for integration with Google Data Studio.

3

This can be achieved with integromat. First you will need to add a sum field to the user table, select the table that you want to count records with a date condition of today. The table that you are counting must have a user connection if it doesn’t already.

Then in Integromat you can setup a search function to search all user records and copy the count field, username and date into a separate static table where you will be able to utilize the chart component.

That is how I would do it anyway, im sure there are other ways as well.

1 Like
4

There’s no way to do this without relying on an external script.

But we did help someone do this in the past. I’ll find the script and share it with you shortly.

It involves the following:

  1. In your users table add a text field call it anything like “Last Update Status”
  2. Copy a script to Google and replace the API keys, field names etc with the values appropriate for your data.
  3. Create a custom pipe and custom task to run daily.

I’ll be back soon with the script

I’ll find the script and paste it here soon.

1 Like
5

Here’s the script:

/*************

* Main Variables

**************/

var Tadabase_Api_Id = 'XXX';

var Tadabase_Api_Key = 'XXXX';

var Tadabase_Api_Secret = 'XXX';

/*************

* Log Table Variables

**************/

var Log_Record_Table_Id = 'lGArg7rmR6'; // 'TABLE ID FOR WHERE TO SAVE THE LOG TABLE';

/*************

* Count Table Variables - This is the table that should be looked at to see how many records added for this user. 

**************/

var Records_To_Count_Table_Id = 'o6WQb5NnBZ'; //'PUT THE TABLE ID FOR THE TABLE TO BE LOOKED AT OF THE RECORDS TO BE COUNTED'; 

var Records_To_Count_User_Id_Field = 'field_37'; //'PUT HERE THE FIELD ID FOR THE FIELD THAT CONNECT TO USERS TABLE'; 

var Records_To_Count_Date_Field = 'field_34'; //'DATE FIELD TO BE USED FOR FILTERING TO ONLY COUNT RECORDS ADDED TODAY.';

/*************

* API Settings

**************/

var Api_Url = 'https://api.tadabase.io/api/v1/data-tables';

var Request_Var = {

        'method' : 'GET',

        'headers': {

           "X-Tadabase-App-id" : Tadabase_Api_Id,

           "X-Tadabase-App-Key" : Tadabase_Api_Key,

           "X-Tadabase-App-Secret" : Tadabase_Api_Secret

        }

    };

/*

/*

This function gets the user ID from the URL and then returns the total records added today in this table for the given User ID. 

*/

function getTotalTodayRecords(userId) {

    var response = UrlFetchApp.fetch(Api_Url+"/"+Records_To_Count_Table_Id+"/records?filters[items][0][field_id]="+Records_To_Count_User_Id_Field+"&filters[items][0][operator]=is&filters[items][0][val]="+userId+"&filters[items][1][field_id]="+Records_To_Count_Date_Field+"&filters[items][1][operator]=is+today&filters[condition]=AND", Request_Var);

    return JSON.parse(response).total_items;

}

function saveTotalForEachUser(data) {

  var request = Request_Var;

  request.method = 'POST';

  request.payload = data;

  var response = UrlFetchApp.fetch(Api_Url+"/"+Log_Record_Table_Id+"/records", request);

  var status = JSON.parse(response).type;

  var json = {

  'status':status

  }; 

  return ContentService.createTextOutput(JSON.stringify(json) ).setMimeType(ContentService.MimeType.JSON);  

}

function doGet(e) {

  

  var userId = e.parameter['userId'];

  

  var total = getTotalTodayRecords(userId);

  

  var data = {

      field_39 : userId, //PUT HERE THE FIELD ID FOR THE FIELD THAT CONNECT TO USERS TABLE

      field_40 : total //NUMBER FIELD IN LOG TABLE WHICH WILL STORE TOTAL RECORDS FROM THAT DAY

      }

  return saveTotalForEachUser(data);

  

}

Here’s some instructions.

Sorry about my sore throat and having to troubleshoot on video.

6

Thanks for the video and the explanation. I think I have to see the video still several times in order to get it running, but I will definitely try.

7

My pleasure.

If you prefer, feel free to share the app with me (moe@tad…) and I’d be happy to assist specifically with your use case.