Populating Google Sheets with Data from TDengine Cloud

TDengine is a high-performance, open-source and enterprise-ready time-series database. With TDengine Cloud, you get all of the benefits of the core TDengine platform without having to worry about maintaining infrastructure. TDengine Cloud is very developer-friendly, and you can use client libraries for a variety of languages, including Python, Node.js, and C/C++, to connect your application to TDengine.

Additionally, TDengine Cloud provides a REST endpoint which gives you tremendous flexibility to connect to TDengine and carry out even complex queries. While enterprises typically use analytics applications like Power BI and Tableau to connect to data sources, and while TDengine supports these applications, SMBs and startups do use more inexpensive solutions like Google Workspace for their enterprise needs. In these cases, Google Apps Script is a fairly powerful tool to perform automation against Google services such as Google Drive and several other Google services.

This article demonstrates how Google Apps Script can be used to fetch data easily from TDengine into Google Sheets, by performing queries against the REST endpoint.

Google Apps Script, for all practical purposes, is JavaScript — but with Google-provided methods that simplify or extend JavaScript functionality. Google provides a web-based IDE for Google Apps Script projects.

The following procedure creates a Google Apps Script project that will prompt the user for a query and for a database name, perform the query, and then populate a spreadsheet with the data. The headers are populated in the first row of the spreadsheet.

1. Create a new Google Sheet document and give it a name.
2. Click Extensions > Apps Script.

3. The Google Apps Script IDE is displayed.

4. Now you can enter your code. The example code is shown here. Comments in the code provide adequate explanations.

function getDataFromTDengine() {
  
  // This function expects the query and db to be supplied
  
  query = displayPrompt("Please enter the query:");
  db = displayPrompt("Please enter the database name you are querying:")
  
  // This is an example token. Log in to your TDengine Cloud account to find your token.
  const TDENGINE_CLOUD_TOKEN="029d9b342xxxxx4c09ef8bf519732d514ae3e69bf47a";
  
  // This is an example URL. Log in to your TDengine Cloud account to find your cloud URL.
  const TDENGINE_CLOUD_URL="https://gw.us-central-1.gcp.cloud.tdengine.com";

  // This is the REST endpoint provided for every TDengine Cloud instance.
  const restEndPoint = "/rest/sql/"

  // Make sure the database name is not null or empty.
  if (db === "" || db === null) {
    alertMessage("You must supply a database name.");
  }
  // Construct the final URL for the request.
  var finalURL = TDENGINE_CLOUD_URL+ restEndPoint + db + "?token="+TDENGINE_CLOUD_TOKEN
  
  // Make sure the query is not null or empty.
  
  if (query === "" || query === null) {
    alertMessage("You must supply a query.");

  }
  
  // POST request with the query in payload.
  var options = {
    'method' : 'post',
    'contentType' : 'application/x-www-form-urlencoded',
    'payload' : query
  };

  // Construct the request.
  const req = UrlFetchApp.getRequest(finalURL,options);
  
  // Make the request.
  let response = UrlFetchApp.fetch(finalURL,options);
  var tderesp;
  
  // Make sure the response is OK.
  if (response.getResponseCode() == 200) { // If HTTP-status is 200-299
    // get the response body.
    tderesp =  response.getContentText();
  } 
  else {
    Logger.log("HTTP-Error: " + response.getResponseCode());
    alertMessage(response.getResponseCode());
  }
  
  // Get the active spreadsheet.
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // Choose the tab - we use the default tab which is always named "Sheet1".
  const sheet = activeSheet.getSheetByName("Sheet1");

  // TDengine returns JSON with the keys - code, column_meta, and data
  // if everything went well.
  // Otherwise it returns code and desc.
  const result = JSON.parse(tderesp);

  // Check for code == 0 (success); otherwise, log error.
  if (result.code == 0) {
    
    // Get the row headers from the column_meta key.
    // Populate row 1.
    for (let x=0; x < result.column_meta.length; x++) {
      var label = result.column_meta[x][0];
      sheet.getRange(1,x+1).setValue(label);
    }
    // Get the data values and populate them starting at row 2.
    for (let i=0; i < result.data.length; i++) {
  
      const row = result.data[i];
      for (let j=0; j < result.data[i].length; j++) {
        sheet.getRange(i+2, j+1).setValue(row[j]);
      }

    }
  }
  else {
    Logger.log("TDengine returned code: " + result.code)
    Logger.log("Description: " + result.desc)
  }
}
// This adds a menu item "TDengine" to the menu.
function onOpen(e) {
  
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('TDengine')
      .addItem('Run Query', 'getDataFromTDengine')
      .addToUi();
}

function alertMessage(mesg) {
  SpreadsheetApp.getUi().alert(mesg);
}

function displayPrompt(mesg) {
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt(mesg);
  return result.getResponseText();
}

5. Save the project and click Deploy in the top right hand corner. Then click Test Deployments.

6. Choose Editor Add-on as the deployment type.

7. Click Create new test > Choose document and select the document you created in Step 1.

8. Select the radio button for the test created in Step 7 and click Execute. This returns you to the spreadsheet. Click on Extensions and verify that the menu item for TDengine is displayed. Then click Run Query.

9. You will be prompted for the query and the database name.
10. The query should then run and populate the spreadsheet with the results.

As you can see, it is fairly straightforward to query TDengine using the REST API to populate a Google Sheet with results. Using the Google Apps Script API for Google Sheets, you can also add charts based on the data that was populated and create simple (or complex) reports.

Once you deploy the script as a real project, the menu item appears in Google Sheets a little differently than when you do a Test Deployment.