Exporting records to Google Sheets
In this article we will discuss how you can export records from your Tadabase app to Google Sheets.
If you would like to back up your data tables in your Tadabase app to Google Sheets, you can export your records via Tadabase's Zapier or Integromat integrations or by using Google Scripts.
In this article we will demonstrate how to export records from a data table in your Tadabase app to Google Sheets using Google Scripts.
At this time, Tadabase does not support native integration with Google Sheets. However, due to popular request from our customers we have provided a Google Script for you to utilize to export your records from your Tadabase app to Google Sheets. Please keep in mind that as Tadabase does not natively integrate with Google Sheets we can not support issues or further customization needs with your export. While we provide this article as a courtesy to our valuable customers, please keep in mind that we can not support any issues you may experience with your export.
To begin your export, open a new Google Sheet and select Extensions > Apps Script:
Next, delete everything in the new script window and 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 = 100;
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 connectionSlug = field['slug'];
var connectionSlugValue = connectionSlug+'_val';
var connectionValue = item[connectionSlugValue];
var vals = Object.keys(values).map(function(key) {
return values[key];
});
if(field.type == 'Join'){
records[i].push( connectionValue[0].val );
} 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 retrieve the API credentials from your app settings by clicking Settings on your app's navigation bar and selecting API Keys:
The Table ID can be located by clicking on your selected data table in the Data Builder and retrieving the values from within the URL of the page:
Once you have updated the credentials in your app, click Save in the script window.
Back in the Google Sheet window, refresh the page and wait about 3-5 seconds. You will see a new menu tab appear called "Records" which you can click on and then select Get All Records:
Click Continue to authorize the script:
Next, click Allow to grant Google Scripts the access to perform this API request:
Finally, click Yes to confirm that you would like to pull all the records from your selected data table:
Within the next few seconds you will begin to see all of your data table records appear in the sheet:
Congratulations, you have successfully exported your data table records from Tadabase to your Google Sheet.
We'd love to hear your feedback.