-2

I create xero private api. I need to know how to connect this api using google apps script for loading api data to google sheet

Elango
  • 15
  • 3

2 Answers2

0

Here's a simple API connection:

function publicAPIs() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('publicAPIs.org')
  sh.activate();
  var rg=sh.getRange(1,1,3,2);
  var vA=rg.getValues();
  var apiObj={};
  for(var i=0;i<vA.length;i++){
    apiObj[vA[i][0]]=vA[i][1];
  }
  var r=UrlFetchApp.fetch(apiObj.baseurl + '/categories');
  var cA=JSON.parse(r);
  var catA=[];
  for(var i=0;i<cA.length;i++){
    catA.push([cA[i]]);
  }
  ss.getSheetByName('NamedRanges').getRange(2,1,catA.length,1).setValues(catA);
  var url=apiObj.baseurl + apiObj.qry.replace('~~~',encodeURIComponent(apiObj.category));
  Logger.log(url);
  var result=UrlFetchApp.fetch(url);
  var response=JSON.parse(result.getContentText());
  Logger.log(response.count);
  var hl=Utilities.formatString('<br />URL:%s', url);
  hl+='<br /><input type="button" value="Exit" onClick="google.script.host.close();" />';
  hl+='<style>td,th{border:1px solid #000;}</style><table><tr><th>No</th><th>API</th><th>Description</th><th>Auth</th><th>HTTPS</th><th>Cors</th><th>Link</th><th>Category</th></tr>';
  for(var i=0;i<response.count;i++){
    hl+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',i+1, response.entries[i].API, response.entries[i].Description,response.entries[i].Auth,response.entries[i].HTTPS,response.entries[i].Cors,response.entries[i].Link,response.entries[i].Category)
  }
  hl+='</table>';
  var userInterface=HtmlService.createHtmlOutput(hl).setWidth(1000);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'publicAPIs.org')
}

Here's what the spreadsheet looks like:

enter image description here

The function also loads the validation rule from a NamedRange.

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thank you, I am facing authorization (RSA key ) problem in Xero Private API. – Elango Dec 11 '18 at 13:46
  • Often Access keys can be placed in the Query string of the URL. You’ll just have to check with the API instructions on the URL that you’re trying to gain access to – Cooper Dec 11 '18 at 17:04
0

alternatively, you may use one of the existing tools or add-ons to sync your data between Xero and Google Sheets. One of the suitable options is described in this article on how to link Xero to Google Sheets. However, you can also check out G-Accon add-on as well. Both tools will let you do it without writing a script.

Richard_Wells
  • 11
  • 1