Save records to Google in batches

Save records to Google in batches: Tadabase help.

  • Not yet re-checked
  • Classic builder

Moved from our previous help site and not yet re-checked against the current product. Last edited Sep 20, 2024.

javascript
/*************
* 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;
      }
  }
}

Was this page helpful?

Something wrong or missing on this page? Tell us.