Google Scripts - Export all records
/*************
* 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];
var slug = field.slug;
if( field.type == 'Connection' || field.type == 'Join' ){
slug += '_val';
}
if( typeof item[ slug ] !== 'undefined' ) {
var arr = new Array();
var values = item[ slug ];
switch(field.type) {
case 'Name' :
var field_index = header.indexOf(field.name);
if( values.hasOwnProperty('title') ) {
records[i].push(values['title'] != "null" ? 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'] != "null" ? 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'] != "null" ? 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'] != "null" ? 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 'Calendar' :
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)) {
var val = values.sort().join(',');
records[i].push(val != "null" ? val : '');
} else{
records[i].push('');
}
break;
case 'Address' :
var field_index = header.indexOf(field.name);
if( values.hasOwnProperty('address') ) {
records[i].push(values['address'] != "null" ? 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'] != "null" ? 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'] != "null" ? 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'] != "null" ? 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'] != "null" ? 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'] != "null" ? 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' :
Logger.log(values)
if( typeof values == 'object' && values != [] && Object.keys(values).length ){
records[i].push( values['url'] != "null" ? values['url'] : '' );
} else {
records[i].push('');
}
break;
case 'Image' :
if( typeof values == 'object' && values != [] && Object.keys(values).length ){
records[i].push( values['src'] != "null" ? values['src'] : '' );
} else {
records[i].push('');
}
break;
case 'Connection' :
case 'Join' :
var field_index = header.indexOf(field.name);
var cfield_id_header = field.name+" > ID";
if( header.indexOf(cfield_id_header) < 0 ) {
header[field_index] = field.name + '> Value';
header.splice(field_index, 0, cfield_id_header);
}
if( typeof values == 'object' && values != [] && Object.keys(values).length ){
var vals = [];
var ids = []
for ( var f in values) {
vals.push(values[f].val)
ids.push(values[f].id)
}
records[i].push( ids.join(',') );
records[i].push( vals.join(',') );
} else {
records[i].push('');
records[i].push('');
}
break;
default :
if( typeof values == 'object' && values != [] && Object.keys(values).length ){
var vals = Object.keys(values).map(function(key) {
return values[key];
});
records[i].push( vals.join(' ') );
} else {
var val = item[field.slug];
val = val != "null" ? val : '';
records[i].push(val);
}
break;
}
}
}
}
// Set Header
Sheet.appendRow(header);
// Records
for ( var i in records) {
Sheet.appendRow(records[i]);
}
}
Did this page help you?