Getting Sugar Data into Google Sheets with Google Apps Script

There's already some info for this topic in and around SugarClub, so just to add to what already exists with some examples from my experience.

I am not a dev, just an admin, but I have managed to do a few handy things along the way and I hope these examples are useful for your own projects.

In this [old] post:   RE: Best Practice for Exporting Sugar Report Data to Google Data Studio  the Tool Skyvia is recommended.   I too would highly recommend Skyvia: https://skyvia.com/  and I use that from a google sheet for specific regular task. As always, many ways to accomplish the same result but Skyvia could be a very powerful tool, with free plan too. We almost used it to export data from sugar into google big query, schedule once a week, but it was accomplished in a different way in the end where we already half the work done already - but Skyvia would have easily accomplished the task.

And from this post:    RE: Get instance storage from Cloud Insights   I was able to figure out how to implement a way to track our Sugar Storage over time from the insights server.  This is the example I thought would be great to share with you in here:

Tracking Sugar Storage Over Time 

Within Insights you can see your current SugarCRM storage, but you can’t view it over time. 

I needed to predict the rate we were growing our storage and the info below shows how I achieved this using google apps script inside Google Sheets.

First though, here's the end result...  

This in my Admin Dash:  I have a dashlet showing our File System Storage and another for the Database Storage.   This allows me to predict when we will hit 100% and come up with a plan to handle that in advance.  You will note the FS storage red line bumps up where we increased it:  I had planned for that months in advance obtaining approval from the business to purchase additional storage. It goes up agin later when we added additional licences too.

This works by:

Google Apps Script runs daily to fetch our insights data from the server, populates a google sheet, various charts are generated off the data sheet.  I published a chart which enables that to be brought right back onto my SugarCRM Admin Dashboard inside Web Page Dashlets…  so I have the info I need at a glance inside my dashboard 

Google Apps Script.

Below is my full apps script to show you how I achieved this, feel free to use it.  Again, I am not a developer, so it might look absolutely horrific, but it works.

• Just for the novices like me:
  • I believe this is pretty much just javascript, with Googleyness added.
  • //this is a comment, the 2 slashes, not read by the computer. Useful for notes.
  • Full disclosure, if you get stuck: ask ChatGPT !

From your Google Sheets menu choose Extensions > Apps Script:

In the Code.gs file I have the code shown below. 

Note - you will see for the username and password I am using the 'PropertiesService'
Probably overkill as nobody else can access this file, but it's a way of not having your username and password written in plain text.
It took some figuring out but the documentation is good.

//CONSTANTS
 const authUrl = "https://YOUR-SUGAR-URL/rest/v11_13/oauth2/token";
 const insightsUrl = "https://sugarcloud-insights-euw2.service.sugarcrm.com/api/v1/app_instance";
 const FQDN = "YOUR-INSTANCE-URL"; //Fully Qualified Domain Name - without the https://
 const now = new Date();
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheetByName("DATA");


function mainFunction(){

 var authData = {
   "grant_type":"password",
   "client_id":"sugar",
   "client_secret":"",
   "username": PropertiesService.getUserProperties().getProperty("LU"),
   "password": PropertiesService.getUserProperties().getProperty("LP"),
   "platform":"base"
 }

 var authOptions = {
  'method' : 'post',
  'contentType': 'application/json',
  // Convert the JavaScript object to a JSON string.
  'payload' : JSON.stringify(authData)
 }

 authResponse = UrlFetchApp.fetch(authUrl, authOptions);
 //Logger.log(authResponse.getContentText());
 var authResponsedata = JSON.parse(authResponse);
 var accT = authResponsedata.access_token;
 // Logger.log(accT);

 insightAuthData = {
  'method': 'get',
  'headers' : {
      'OAuth-Token' : accT,
      'X-Sugar-FQDN' : FQDN
    },
 "muteHttpExceptions": true,
 };
//

//FETCH & PARSE
 
 //FETCH
 var insightResponse = UrlFetchApp.fetch(insightsUrl ,insightAuthData);
 Logger.log(insightResponse.getContentText());
 
 //JSON.PARSE
 var insightResponseData = JSON.parse(insightResponse);
 Logger.log("insightResponseData:",insightResponseData);
//

//ASSIGN VARIABLES
 var ain = insightResponseData.app_instance_name;
 var tSizeKb = insightResponseData.total_allowed_usage_kbytes;
 var allowDB = insightResponseData.allowance.database;
 var allowFS = insightResponseData.allowance.filesystem;
 var sizeInfo_PD_FS = insightResponseData.size_infos[0].filesystem_size_kbytes;
 var sizeInfo_PD_DB = insightResponseData.size_infos[0].database_size_kbytes;
 var sizeInfo_SB_FS = insightResponseData.size_infos[1].filesystem_size_kbytes;
 var sizeInfo_SB_DB = insightResponseData.size_infos[1].database_size_kbytes;

//

//APPEND TO 'DATA' SHEET
 var data = [now,ain,tSizeKb,allowDB,allowFS,sizeInfo_PD_FS,sizeInfo_PD_DB,sizeInfo_SB_FS,sizeInfo_SB_DB];
 sheet.appendRow(data);
//
}

You will also have to authorise some other services - it usually prompts for this when you run it. You'll see I also have a appscript.json file, I forget now if I wrote that or if it happened by magic...  I will add the contents of that too below. It relates to the engine it runs upon, and the auth scopes enabled.

appscript.json

{
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ],

  "timeZone": "Europe/London",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

I also have a .gs file for setting and checking the properties service - I changed my password one day and this broke the whole thing, so I made this file to set and get the proprerties service.  I would advise looking up the documentation if wish to use the properties servce.  To start off, you can just type in your username and pass.

This all gets the data from your insights server into the google sheet, my tab id called DATA:

In apps script triggers, you can set it to run as ofeten as you need it. Mine is daily.

The rest is good old spreadsheet work, I created a Dash tab, in there I do things like converting to % of storage, then create a chart.

Google sheets allows publish a chart and woth that you get a public URL.

Use that public URL back in a dashboard webpage dashlet, et viola...  you have your Insights storage back inside your SugarCRM Dashboard.

Hope you like it.

Luke.

(I might edit and add more examples - but I am out of time just now)