search-icon

Answered
API and GOOGLE SHEETS

started a topic about 5 years 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("")


Logger.log(response.getContentText()

)}


api-auth-accountid & api-auth-applicationkey


Request failed for 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 = "<span class="fr-marker" data-id="0" data-type="true" style="display: inline-block; line-height: 0;"></span><span class="fr-marker" data-id="0" data-type="false" style="display: inline-block; line-height: 0;"></span>" + 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 = "";
      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

  • To get API credentials into Google Sheets, you will need to create a project in the Google Cloud Console and enable the necessary APIs for your project. Here are the general steps you need to follow:

    Go to the Google Cloud Console and create a new project.

    Once you have created the project, go to the APIs & Services Dashboard.

    Click on the "+ ENABLE APIS AND SERVICES" button.

    Search for the API you want to use (e.g. Google Calendar API) and enable it for your project. bluey

  • Would you consider working with mariogames.io to integrate a GoogleSheet data sync?

    https://mariogames.io

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