Save records to Google in batches
/*************
* 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 = 'o6WQb5NnBZ';
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', 'getAllRecords')
.addItem('Clear Sheet', 'removeRecords')
.addToUi();
}
/*
Function : getAllRecords
Descriptions : Get All Records
Attach with menu : Get All Records
*/
function getAllRecords() {
SpreadsheetApp.getUi();
var r = Browser.msgBox('Confirmation', 'Are you sure want Get All Records ?', Browser.Buttons.YES_NO);
if(r == 'yes'){
getRecords();
}
}
/*
Function : removeRecords
Descriptions : Remove All Records
Attach with menu : Clear Sheet
*/
function removeRecords() {
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 : getData
Descriptions : get API records
*/
function getData(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();
// Set Header
var header = cols.fields.map(function(f){ return f.name } );
Sheet.appendRow(header);
var total_header = header.length;
var header_row = Sheet.getLastRow();
var allitems = new Array();
var page = 1, limit = 1000, $flag = true;
while ($flag) {
var response = getData(limit, page);
if (response.hasOwnProperty("type") && response.hasOwnProperty("items") && response.type=='success') {
for (var i in response.items) {
record = new Array();
var item = response.items[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')) {
record.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')) {
record.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')) {
record.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')) {
record.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')) {
record.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')) {
record.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':
record.push(values['link']);
break;
case 'Checkbox':
case 'Multi-Select':
if (Array.isArray(values)) {
record.push(values.sort().join(','));
} else {
record.push('');
}
break;
case 'Address':
var field_index = header.indexOf(field.name);
if (values.hasOwnProperty('address')) {
record.push(values['address']);
if (header.indexOf(field.name + " > Address") < 0) {
header.splice(field_index, 0, field.name + " > Address");
field_index++;
}
}
if (values.hasOwnProperty('address2')) {
record.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')) {
record.push(values['city']);
if (header.indexOf(field.name + " > City") < 0) {
header.splice(field_index, 0, field.name + " > City");
field_index++;
}
}
if (values.hasOwnProperty('state')) {
record.push(values['state']);
if (header.indexOf(field.name + " > State") < 0) {
header.splice(field_index, 0, field.name + " > State");
field_index++;
}
}
if (values.hasOwnProperty('country')) {
record.push(values['country']);
if (header.indexOf(field.name + " > Country") < 0) {
header.splice(field_index, 0, field.name + " > Country");
field_index++;
}
}
if (values.hasOwnProperty('zip')) {
record.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) {
record.push(values['url']);
} else {
record.push('');
}
break;
case 'Image':
if (typeof values === 'object' && (values != [] && values != null) && Object.keys(values).length) {
record.push(values['src']);
} else {
record.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' && connectionValue.length) {
record.push(connectionValue[0].val);
} else {
record.push(vals.join(' '));
}
} else {
record.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];
});
record.push(vals.join(' '));
} else {
record.push(item[field.slug]);
}
break;
}
}
}
//Refresh Header if change
if(header.length != total_header){
Sheet.getRange(header_row, 1, 1, header.length).setValues([header]);
}
Sheet.appendRow(record);
}
}
if (!response.hasOwnProperty("total_pages") || page >= response.total_pages) {
$flag = false;
} else {
page += 1;
}
}
}
We'd love to hear your feedback.