search-icon

Answered
API and GOOGLE SHEETS

started a topic over 1 year ago

Hi,


How do i get the API credentials into google sheets when i run the availability get function?


function myFunction() {

var response = UrlFetchApp.fetch("https://inventory.dearsystems.com/ExternalApi/v2/ref/productavailability?Page=1&Limit=1000")

Logger.log(response.getContentText()

)}


api-auth-accountid & api-auth-applicationkey


Request failed for https://inventory.dearsystems.com/ExternalApi/v2/ref/productavailability?Page=1&Limit=1000 returned code 403. Truncated server response: Incorrect credentials! (use muteHttpExceptions option to examine full response) (line 3, file "C

  • Here is the code I use to make API requests from Google Apps Script: 

      var DEARapiauthaccountid = "YOUR-API-ID-HERE";
      var DEARapiauthapplicationkey = "YOUR-API-KEY-HERE";
      var DEARoptions = {'headers' : {"api-auth-accountid" : DEARapiauthaccountid,
                      "api-auth-applicationkey" : DEARapiauthapplicationkey,
                      "Accept" : "application/json"
                               }};
      
    
    
      var url = "https://inventory.dearsystems.com/ExternalApi/v2/saleList?Page=1&Limit=100&Search=" + SOorINVnumber;
      var dataAll = JSON.parse(UrlFetchApp.fetch(url, DEARoptions).getContentText());
      Logger.log(dataAll);
    

     


    1 person likes this
  • Thank you Tristan, you are a great help.

    i have modified to get product availability, 


    my next challenge is to get it into google sheets, can you assist?


     

    function myFunction() {
        var DEARapiauthaccountid = "xxxxx";
      var DEARapiauthapplicationkey = "xxxxx";
      var DEARoptions = {'headers' : {"api-auth-accountid" : DEARapiauthaccountid,
                      "api-auth-applicationkey" : DEARapiauthapplicationkey,
                      "Accept" : "application/json"
                               }};
      
    
    
      var url = "https://inventory.dearsystems.com/ExternalApi/v2/ref/productavailability?Page=1&Limit=1000%22";
      var dataAll = JSON.parse(UrlFetchApp.fetch(url, DEARoptions).getContentText());
      Logger.log(dataAll);
    }
    

     

  • The Google Apps Script website is really good and has some good tutorials as well:

    https://developers.google.com/apps-script/reference/spreadsheet/


  • Dear Team,


    Would you consider working with Flatly.io to integrate a GoogleSheet data sync?
    https://flatly.io/


    They have a very user-friendly interface that would greatly help your users trying to integrate DEAR to other tools.


    1 person likes this
  • Hi All,

    If you invest some time with the API and google sheets you can get tons of work done in an instant without unnecessary keystrokes.  First you need to get the code from github, and then perform a very slight modification shown below to get your DEAR credentials into the mix.


    Add this function to your sheet and away you go.

    https://github.com/bradjasper/ImportJSON


    Here is a good tutorial to get you started.


    Then add code like this to your script file:

    function dearCredentials(myOptions){

    return myOptions={

       'method' : 'get',

       'contentType': 'application/json',

      'headers':{

        'api-auth-accountid': 'your accountid here','api-auth-applicationkey': 'your app key here'

      },

      'muteHttpExceptions': true,

     };

    };


    Finally, insert this the second line below into the ImportJSONAdvanced() function shown:


    function ImportJSONAdvanced(url, fetchOptions, query, parseOptions, includeFunc, transformFunc) {

      var fetchOptions=dearCredentials();

      var jsondata = UrlFetchApp.fetch(url, fetchOptions);

      var object = JSON.parse(jsondata.getContentText());

      

      return parseJSONObject_(object, query, parseO

    .....


    That should do it.  Then you can create a spreadsheet function that looks like this:

    =ImportJSON(A1,,"noHeaders"), where A1 points to a cell with a DEAR API URL.


    Sam

Login or Signup to post a comment
Log in or Sign up to post a comment