NextGIS Web Vector Layer to Synchronizable Google Sheets Document

I faced a situation where there is a regularly updating spatial data flow, collected inside a NextGIS Web vector layer container. A user wanted to work with this data inside the Google Sheets interface to draw plots, apply specific Google Drive filters, and more. This was a great opportunity to explore the Apps Script mechanism of Google Sheets.

To illustrate the case, I'll use an automatically updatable vector layer from the demo: NASA FIRMS data for Colorado, daily updates. This data stored in NextGIS Web can be accessed in various ways:

• Via NextGIS Web UI, as a table or map layer
• Via NextGIS Web API, as JSON, GeoJSON, GeoPackage, etc.
• Via OGC Services: WMS, WFS, OGC API — Features
• Via raster or vector tiles services

Accessing it as JSON from the API seems like the most natural way to integrate the data into Google Drive. There is a simple endpoint: https://demo.nextgis.com/api/resource/5939/feature/ that returns a JSON representation of all vector layer features.

I created a new empty Google Sheets document and went to Extensions → Apps Script menu:

Then, I wrote simple code to obtain NextGIS Web vector layer data as a table, setting up the first row as a filter along the way:

function fetchDataFromAPI() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var url = 'https://demo.nextgis.com/api/resource/5939/feature/'; 
  
  var response = UrlFetchApp.fetch(url);
  var json = response.getContentText();
  var data = JSON.parse(json);
  var headers = Object.keys(data[0].fields);
  
  if (sheet.getLastRow() === 0) {
    sheet.appendRow(headers);
  }

  if (sheet.getLastRow() > 1) {
    sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clearContent();
  }

  var values = data.map(function(row) {
    return headers.map(function(header) {

      return row.fields[header] !== null ? row.fields[header] : '';
    });
  });

  if (values.length > 0) {
    sheet.getRange(2, 1, values.length, headers.length).setValues(values);
  }

  if (!sheet.getFilter()) {
    sheet.getRange(1, 1, 1, headers.length).createFilter();
  }
}

Thousands of records are processed in several seconds, which is more than acceptable for my use case.

Next, I set up triggers to automatically update the table from the layer every day:

Now the user has an automatically synced Google Sheets version of their data. Thanks to the NextGIS Web API and Apps Scripts, this integration is straightforward, without the need for additional services or hosting.