If you'd like to pull data from a table in your Tadabase app and save it to Google sheets you can do that with our Zapier or Integromat integration or by using Google Scripts. 


In this short support document we'll go over the basics of how to back up your table to Google Sheets. 


Please make sure you have API enabled in your app, if you don't please contact us at support@tadabase.io and we'll enable API access in your account. 


To start, open up a new Google Sheet and under Tools click Script editor


Next, delete everything in the new script window then copy/paste the script below into the editor. 

/*************
* Variables
**************/
var Tadabase_App_Id = '<REPALCE WITH YOU APP ID>';
var Tadabase_Api_Key = '<REPLACE WITH YOUR API KEY>';
var Tadabase_Api_Secret = '<REPLACE WITH YOUR API SECRET>';

/*************
* API Settings
**************/
var Api_Url = 'https://api.tadabase.io/api/v1/data-tables';
var Table_Id = '<REPLACE WITH YOUR TABLE ID>';
var Request_Var = {
        'method' : 'GET',
        'headers': {
           "X-Tadabase-App-id" : Tadabase_App_Id,
           "X-Tadabase-App-Key" : Tadabase_Api_Key,
           "X-Tadabase-App-Secret" : Tadabase_Api_Secret
        }
    };

/*************
* Custom Menu
**************/
/* 
Function : onOpen
Descriptions : Add Custom Menu in Spreatsheet menus
Attach with menu : Clear Sheet
*/
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Records')
      .addItem('Get All Records', 'getall_records')
      .addItem('Clear Sheet', 'remove_records')
      .addToUi();
}

/* 
Function : getall_records
Descriptions : Get All Records
Attach with menu : Get All Records
*/
function getall_records() {
  SpreadsheetApp.getUi();
  var r = Browser.msgBox('Confirmation', 'Are you sure want Get All Records ?', Browser.Buttons.YES_NO);
  
  if(r == 'yes'){
    getRecords();
  }
}

/* 
Function : remove_records
Descriptions : Remove All Records
Attach with menu : Clear Sheet
*/
function remove_records() {
  SpreadsheetApp.getUi();
  var r = Browser.msgBox('Confirmation', 'Are you sure want Delete All Records ?', Browser.Buttons.YES_NO);
  if(r == 'yes'){
    var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var Sheet = Spreadsheet.getActiveSheet();
    Sheet.clear();
  }
  
}
/*End Custom Menu*/

/* 
Function : get_data
Descriptions : get API records
*/
function get_data(limit, page) {
  
  var response = UrlFetchApp.fetch(Api_Url+"/"+Table_Id+"/records?limit="+limit+"&page="+page, Request_Var);
  return JSON.parse(response);
}

/* 
Function : getRecords
Descriptions : Get All Records and show on sheet
*/
function getRecords() {
  
  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet = Spreadsheet.getActiveSheet();
  var response = UrlFetchApp.fetch(Api_Url+"/"+Table_Id+"/fields", Request_Var);
  var cols = JSON.parse(response);
  
  var records = new Array();
  var header = new Array();
 
  for ( var i in cols.fields) {
    var d = cols.fields[i];
    header.push(d.name);
  }
  
  var allitems = new Array(); 
  var page = 1, limit = 10;
  var rec = get_data(limit,page);
  allitems = rec.items;
  if(rec.total_pages > 1) {
    for (var i = 2; i <= rec.total_pages; i++) { 
      var rec = get_data(limit, i);
      for ( var r in rec.items) {
        allitems.push(rec.items[r]);
      }
    }
  }
  
  for ( var i in allitems) {
    records[i] = new Array();
    var item = allitems[i];
    for ( var iv in cols.fields) {
       var field = cols.fields[iv];
       if( typeof item[ field.slug ] !== 'undefined' ) {
         var arr = new Array();
         var values = item[ field.slug ];
           switch(field.type) {
             case 'Name' :

                    var field_index = header.indexOf(field.name);
                    if( values.hasOwnProperty('title') ) {
                      records[i].push(values['title']);
                      if( header.indexOf(field.name+" > Title") < 0 ) {
                         header.splice(field_index, 0, field.name+" > Title");
                         field_index++;
                       }
                    }
                    if( values.hasOwnProperty('first_name') ) {
                      records[i].push(values['first_name']);
                      if( header.indexOf(field.name+" > First Name") < 0 ) {
                         header.splice(field_index, 0, field.name+" > First Name");
                         field_index++;
                      }
                    }
                    if( values.hasOwnProperty('middle_name') ) {
                      records[i].push(values['middle_name']);
                      if( header.indexOf(field.name+" > Middel Name") < 0 ) {
                         header.splice(field_index, 0, field.name+" > Middel Name");
                         field_index++;
                      }
                    }
                    if( values.hasOwnProperty('last_name') ) {
                      records[i].push(values['last_name']);
                      if( header.indexOf(field.name+" > Last Name") < 0 ) {
                         header.splice(field_index, 0, field.name+" > Last Name");
                         field_index++;
                      }
                    }
                    //Remove Name Field from header
                    if( header.indexOf(field.name) > 0 ) {
                       header.splice( header.indexOf(field.name), 1 );
                    }
                    break;

              case 'Date Range' :
               
                    var field_index = header.indexOf(field.name);
                    if( values.hasOwnProperty('start') ) {
                      records[i].push(values['start']);
                      if( header.indexOf(field.name+" > Start Date") < 0 ) {
                        header.splice(field_index, 0, field.name+" > Start Date");
                        field_index++;
                      }
                    }
                    if( values.hasOwnProperty('end') ) {
                      records[i].push(values['end']);
                      if( header.indexOf(field.name+" > End Date") < 0 ) {
                        header.splice(field_index, 0, field.name+" > End Date");
                        field_index++;
                      }
                    }
                    //Remove Date Range Field from header
                    if( header.indexOf(field.name) > 0 ) {
                       header.splice( header.indexOf(field.name), 1 );
                    }
                    
                    break;
                       
               case 'Link' :
               
                    records[i].push(values['link']);
                    break;
                       
               case 'Checkbox' :
               case 'Multi-Select' :

                    if(Array.isArray(values)) {
                       records[i].push(values.sort().join(','));
                    } else{
                      records[i].push('');
                    }
                    break;

              case 'Address' :
                     
                     var field_index = header.indexOf(field.name);
                     if( values.hasOwnProperty('address') ) {
                       records[i].push(values['address']);
                       if( header.indexOf(field.name+" > Address") < 0 ) {
                         header.splice(field_index, 0, field.name+" > Address");
                         field_index++;
                       }   
                     }
                     if( values.hasOwnProperty('address2') ) {
                       records[i].push(values['address2']);
                       if( header.indexOf(field.name+" > Address 2") < 0 ) {
                         header.splice(field_index, 0, field.name+" > Address 2");
                         field_index++;
                       }
                     }
                     if( values.hasOwnProperty('city') ) {
                       records[i].push(values['city']);
                       if( header.indexOf(field.name+" > City") < 0 ) {
                         header.splice(field_index, 0, field.name+" > City");
                         field_index++;
                       }
                     }
                     if( values.hasOwnProperty('state') ) {
                       records[i].push(values['state']);
                       if( header.indexOf(field.name+" > State") < 0 ) {
                         header.splice(field_index, 0, field.name+" > State");
                         field_index++;
                       }
                     }
                     if( values.hasOwnProperty('country') ) {
                       records[i].push(values['country']);
                       if( header.indexOf(field.name+" > Country") < 0 ) {
                         header.splice(field_index, 0, field.name+" > Country");
                         field_index++;
                       }
                     }
                     if( values.hasOwnProperty('zip') ) {
                       records[i].push(values['zip']);
                       if( header.indexOf(field.name+" > Zip") < 0 ) {
                         header.splice(field_index, 0, field.name+" > Zip");
                         field_index++;
                       }
                     }
                     
                     //Remove Address Field from header
                     if( header.indexOf(field.name) > 0 ) {
                       header.splice( header.indexOf(field.name), 1 );
                     }
                     break;
               
              case 'File' :
               
                     if( typeof values === 'object' && (values != [] && values != null)  && Object.keys(values).length ){                       
                        records[i].push( values['url'] );
                      } else {
                        records[i].push('');
                      }
                      break;
               
              case 'Image' :
               
                     if( typeof values === 'object' && (values != [] && values != null) && Object.keys(values).length ){                       
                        records[i].push( values['src'] );
                      } else {
                        records[i].push('');
                      }
                      break;
               
              case 'Connection' :
              case 'Join' :
               
                     if( typeof values === 'object' && (values != [] && values != null) && Object.keys(values).length  ){
                       var vals = Object.keys(values).map(function(key) {
                           return values[key];
                         });
                       if(field.type == 'Join'){
                         records[i].push( vals.join(',') );
                       } else {
                         records[i].push( vals.join(' ') );
                       }
                     } else {
                        records[i].push('');
                     }
                     break;
                     
             default :
               
                     if( typeof values === 'object' && (values != [] && values != null) && Object.keys(values).length ){
                       var vals = Object.keys(values).map(function(key) {
                           return values[key];
                         });
                       records[i].push( vals.join(' ') );
                     } else {
                        records[i].push(item[field.slug]);
                     }
                     break;
          }
       }
    }
  }
  
  // Set Header
  Sheet.appendRow(header);
  
 // Records  
  for ( var i in records) {
    Sheet.appendRow(records[i]);
  }

}


Be sure to replace the App ID, API Key, API Secret and Table ID with the credentials from your app. 



You can get the API credentials from your app settings


The table ID is located by clicking on a table in the Data Builder and looking in the URL of the page. 



Once you've updated the credentials in your app click on Save in the script window. 


Back in the Google Sheet window, refresh the page and wait about 3-5 seconds. You'll see a new menu appear called "Records" you can select it and choose "Get All Records"



You'll be asked to authorize, click Continue 

Next select Allow to grant Google Scripts the access to perform this API request


Finally confirm that you'd like to get all the records from the table 



Over the next few seconds you'll start seeing all your records appear in the sheet. 



What's next? 


  • Once you have your data in Google Sheets you can easily use Google Data Studio to create dynamic and powerful charts. 
  • Edit the script to meet your unique needs
  • Checkout our Zapier and Integromat integration for even more flexibility and control.