Google Scripts - Export all records
xxxxxxxxxx1
/*************2
* Variables3
**************/4
var Tadabase_App_Id = '<REPALCE WITH YOU APP ID>';5
var Tadabase_Api_Key = '<REPLACE WITH YOUR API KEY>';6
var Tadabase_Api_Secret = '<REPLACE WITH YOUR API SECRET>';7
8
/*************9
* API Settings10
**************/11
var Api_Url = 'https://api.tadabase.io/api/v1/data-tables';12
var Table_Id = '<REPLACE WITH YOUR TABLE ID>';13
var Request_Var = {14
'method' : 'GET',15
'headers': {16
"X-Tadabase-App-id" : Tadabase_App_Id,17
"X-Tadabase-App-Key" : Tadabase_Api_Key,18
"X-Tadabase-App-Secret" : Tadabase_Api_Secret19
}20
};21
22
/*************23
* Custom Menu24
**************/25
/* 26
Function : onOpen27
Descriptions : Add Custom Menu in Spreatsheet menus28
Attach with menu : Clear Sheet29
*/30
function onOpen() {31
var ui = SpreadsheetApp.getUi();32
// Or DocumentApp or FormApp.33
ui.createMenu('Records')34
.addItem('Get All Records', 'getall_records')35
.addItem('Clear Sheet', 'remove_records')36
.addToUi();37
}38
39
/* 40
Function : getall_records41
Descriptions : Get All Records42
Attach with menu : Get All Records43
*/44
function getall_records() {45
SpreadsheetApp.getUi();46
var r = Browser.msgBox('Confirmation', 'Are you sure want Get All Records ?', Browser.Buttons.YES_NO);47
48
if(r == 'yes'){49
getRecords();50
}51
}52
53
/* 54
Function : remove_records55
Descriptions : Remove All Records56
Attach with menu : Clear Sheet57
*/58
function remove_records() {59
SpreadsheetApp.getUi();60
var r = Browser.msgBox('Confirmation', 'Are you sure want Delete All Records ?', Browser.Buttons.YES_NO);61
if(r == 'yes'){62
var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();63
var Sheet = Spreadsheet.getActiveSheet();64
Sheet.clear();65
}66
67
}68
/*End Custom Menu*/69
70
/* 71
Function : get_data72
Descriptions : get API records73
*/74
function get_data(limit, page) {75
76
var response = UrlFetchApp.fetch(Api_Url+"/"+Table_Id+"/records?limit="+limit+"&page="+page, Request_Var);77
return JSON.parse(response);78
}79
80
/* 81
Function : getRecords82
Descriptions : Get All Records and show on sheet83
*/84
function getRecords() {85
86
var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();87
var Sheet = Spreadsheet.getActiveSheet();88
var response = UrlFetchApp.fetch(Api_Url+"/"+Table_Id+"/fields", Request_Var);89
var cols = JSON.parse(response);90
91
var records = new Array();92
var header = new Array();93
94
for ( var i in cols.fields) {95
var d = cols.fields[i];96
header.push(d.name);97
}98
99
var allitems = new Array(); 100
var page = 1, limit = 10;101
var rec = get_data(limit,page);102
allitems = rec.items;103
if(rec.total_pages > 1) {104
for (var i = 2; i <= rec.total_pages; i++) { 105
var rec = get_data(limit, i);106
for ( var r in rec.items) {107
allitems.push(rec.items[r]);108
}109
}110
}111
112
for ( var i in allitems) {113
records[i] = new Array();114
var item = allitems[i];115
for ( var iv in cols.fields) {116
var field = cols.fields[iv];117
var slug = field.slug;118
if( field.type == 'Connection' || field.type == 'Join' ){119
slug += '_val';120
}121
if( typeof item[ slug ] !== 'undefined' ) {122
var arr = new Array();123
var values = item[ slug ];124
125
switch(field.type) {126
case 'Name' :127
var field_index = header.indexOf(field.name);128
if( values.hasOwnProperty('title') ) {129
records[i].push(values['title'] != "null" ? values['title'] : '');130
if( header.indexOf(field.name+" > Title") < 0 ) {131
header.splice(field_index, 0, field.name+" > Title");132
field_index++;133
}134
}135
if( values.hasOwnProperty('first_name') ) {136
records[i].push(values['first_name'] != "null" ? values['first_name'] : '');137
if( header.indexOf(field.name+" > First Name") < 0 ) {138
header.splice(field_index, 0, field.name+" > First Name");139
field_index++;140
}141
}142
if( values.hasOwnProperty('middle_name') ) {143
records[i].push(values['middle_name'] != "null" ? values['middle_name'] : '');144
if( header.indexOf(field.name+" > Middel Name") < 0 ) {145
header.splice(field_index, 0, field.name+" > Middel Name");146
field_index++;147
}148
}149
if( values.hasOwnProperty('last_name') ) {150
records[i].push(values['last_name'] != "null" ? values['last_name'] : '');151
if( header.indexOf(field.name+" > Last Name") < 0 ) {152
header.splice(field_index, 0, field.name+" > Last Name");153
field_index++;154
}155
}156
//Remove Name Field from header157
if( header.indexOf(field.name) > 0 ) {158
header.splice( header.indexOf(field.name), 1 );159
}160
break;161
162
case 'Calendar' :163
164
var field_index = header.indexOf(field.name);165
if( values.hasOwnProperty('start') ) {166
records[i].push(values['start']);167
if( header.indexOf(field.name+" > Start Date") < 0 ) {168
header.splice(field_index, 0, field.name+" > Start Date");169
field_index++;170
}171
}172
if( values.hasOwnProperty('end') ) {173
records[i].push(values['end']);174
if( header.indexOf(field.name+" > End Date") < 0 ) {175
header.splice(field_index, 0, field.name+" > End Date");176
field_index++;177
}178
}179
//Remove Date Range Field from header180
if( header.indexOf(field.name) > 0 ) {181
header.splice( header.indexOf(field.name), 1 );182
}183
184
break;185
186
case 'Link' :187
188
records[i].push(values['link']);189
break;190
191
case 'Checkbox' :192
case 'Multi-Select' :193
194
if(Array.isArray(values)) {195
var val = values.sort().join(',');196
records[i].push(val != "null" ? val : '');197
} else{198
records[i].push('');199
}200
break;201
202
case 'Address' :203
204
var field_index = header.indexOf(field.name);205
if( values.hasOwnProperty('address') ) {206
records[i].push(values['address'] != "null" ? values['address'] : '');207
if( header.indexOf(field.name+" > Address") < 0 ) {208
header.splice(field_index, 0, field.name+" > Address");209
field_index++;210
} 211
}212
if( values.hasOwnProperty('address2') ) {213
records[i].push(values['address2'] != "null" ? values['address2'] : '');214
if( header.indexOf(field.name+" > Address 2") < 0 ) {215
header.splice(field_index, 0, field.name+" > Address 2");216
field_index++;217
}218
}219
if( values.hasOwnProperty('city') ) {220
records[i].push(values['city'] != "null" ? values['city'] : '');221
if( header.indexOf(field.name+" > City") < 0 ) {222
header.splice(field_index, 0, field.name+" > City");223
field_index++;224
}225
}226
if( values.hasOwnProperty('state') ) {227
records[i].push(values['state'] != "null" ? values['state'] : '');228
if( header.indexOf(field.name+" > State") < 0 ) {229
header.splice(field_index, 0, field.name+" > State");230
field_index++;231
}232
}233
if( values.hasOwnProperty('country') ) {234
records[i].push(values['country'] != "null" ? values['country'] : '');235
if( header.indexOf(field.name+" > Country") < 0 ) {236
header.splice(field_index, 0, field.name+" > Country");237
field_index++;238
}239
}240
if( values.hasOwnProperty('zip') ) {241
records[i].push(values['zip'] != "null" ? values['zip'] : '');242
if( header.indexOf(field.name+" > Zip") < 0 ) {243
header.splice(field_index, 0, field.name+" > Zip");244
field_index++;245
}246
}247
248
//Remove Address Field from header249
if( header.indexOf(field.name) > 0 ) {250
header.splice( header.indexOf(field.name), 1 );251
}252
break;253
254
case 'File' :255
Logger.log(values)256
if( typeof values == 'object' && values != [] && Object.keys(values).length ){ 257
records[i].push( values['url'] != "null" ? values['url'] : '' );258
} else {259
records[i].push('');260
}261
break;262
263
case 'Image' :264
265
if( typeof values == 'object' && values != [] && Object.keys(values).length ){ 266
records[i].push( values['src'] != "null" ? values['src'] : '' );267
} else {268
records[i].push('');269
}270
break;271
272
case 'Connection' :273
case 'Join' :274
275
var field_index = header.indexOf(field.name);276
var cfield_id_header = field.name+" > ID";277
if( header.indexOf(cfield_id_header) < 0 ) {278
header[field_index] = field.name + '> Value'; 279
header.splice(field_index, 0, cfield_id_header);280
}281
282
if( typeof values == 'object' && values != [] && Object.keys(values).length ){283
var vals = [];284
var ids = []285
for ( var f in values) {286
vals.push(values[f].val)287
ids.push(values[f].id)288
}289
records[i].push( ids.join(',') );290
records[i].push( vals.join(',') );291
} else {292
records[i].push('');293
records[i].push('');294
}295
break;296
297
default :298
299
if( typeof values == 'object' && values != [] && Object.keys(values).length ){300
var vals = Object.keys(values).map(function(key) {301
return values[key];302
});303
records[i].push( vals.join(' ') );304
} else {305
var val = item[field.slug];306
val = val != "null" ? val : '';307
records[i].push(val);308
}309
break;310
}311
}312
}313
}314
315
// Set Header316
Sheet.appendRow(header);317
318
// Records 319
for ( var i in records) {320
Sheet.appendRow(records[i]);321
}322
323
}
We'd love to hear your feedback.