Google Sheets Integration with the API

Google Sheets is one of the easiest ways to track, share, and analyze IoT data without building a custom dashboard. This tutorial connects your SiliconWit.IO devices to a Google Sheet that updates automatically.

You do not need to install anything. Google Apps Script runs in the browser and can call the SiliconWit.IO Public API directly.

Why Use Google Sheets with the API?

The SiliconWit.IO dashboard works well for real-time monitoring, but Google Sheets gives you:

• Shareable reports that non-technical team members can view and edit
• Custom charts and graphs using Sheets’ built-in charting tools
• Formulas and calculations like averages, trends, and conditional formatting
• Automatic updates on a schedule (hourly, daily) without manual work
• Historical log that grows over time, separate from your plan’s data retention

What You Will Build

A Google Sheet with two sheets (tabs):

1. Current Status - shows the latest reading from every device, refreshed on a schedule
2. History Log - appends a new row every hour with readings from all devices

Prerequisites

Requirement	Details
Google account	For Google Sheets and Apps Script
SiliconWit.IO Account	Starter plan or above
API Key	Created from Dashboard > Settings > API Keys

Step 1: Create the Spreadsheet

1. Go to sheets.google.com and create a new spreadsheet
2. Name it SiliconWit.IO Data
3. Rename the first sheet tab to Current Status
4. Add headers in row 1:

A	B	C	D	E	F
Device Name	Status	Last Seen	Temperature	Humidity	Updated

5. Create a second sheet tab called History Log
6. Add headers in row 1:

A	B	C	D	E
Timestamp	Device Name	Temperature	Humidity	Status

Step 2: Open the Script Editor

1. In your spreadsheet, go to Extensions > Apps Script
2. This opens the Apps Script editor in a new tab
3. Delete the default myFunction code

Step 3: Add the Script

Paste the following code into the script editor:

// ========== CONFIGURATION ==========
const API_KEY = "swk_your_key_here";
const BASE_URL = "https://api.siliconwit.io/v1";

// Fields to extract from telemetry (adjust to match your devices)
const FIELDS = ["temperature", "humidity"];

// ========== API HELPERS ==========
function apiGet(endpoint) {
  const options = {
    method: "get",
    headers: { Authorization: "Bearer " + API_KEY },
    muteHttpExceptions: true,
  };
  const response = UrlFetchApp.fetch(BASE_URL + endpoint, options);
  if (response.getResponseCode() !== 200) {
    throw new Error("API error: " + response.getContentText());
  }
  return JSON.parse(response.getContentText());
}

function getAllDevices() {
  const devices = [];
  let offset = 0;
  while (true) {
    const result = apiGet("/devices?limit=100&offset=" + offset);
    if (!result.data || result.data.length === 0) break;
    devices.push(...result.data);
    offset += result.data.length;
  }
  return devices;
}

function getLatestTelemetry(deviceId) {
  const result = apiGet("/devices/" + deviceId + "/telemetry/latest");
  return result.data;
}

// ========== CURRENT STATUS ==========
function refreshCurrentStatus() {
  const sheet =
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Current Status");
  const devices = getAllDevices();
  const now = new Date().toISOString();

  // Clear old data (keep headers)
  if (sheet.getLastRow() > 1) {
    sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clear();
  }

  const rows = [];
  for (const device of devices) {
    const telemetry = getLatestTelemetry(device.id);
    const row = [
      device.name,
      device.status,
      device.last_seen || "Never",
    ];

    // Add each configured field
    for (const field of FIELDS) {
      if (telemetry && telemetry.data && telemetry.data[field] !== undefined) {
        row.push(telemetry.data[field]);
      } else {
        row.push("");
      }
    }

    row.push(now);
    rows.push(row);
  }

  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
  }

  // Format the Last Seen and Updated columns
  SpreadsheetApp.getActiveSpreadsheet().toast(
    "Updated " + rows.length + " devices",
    "Refresh Complete"
  );
}

// ========== HISTORY LOG ==========
function appendHistoryLog() {
  const sheet =
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("History Log");
  const devices = getAllDevices();
  const now = new Date().toISOString();

  const rows = [];
  for (const device of devices) {
    const telemetry = getLatestTelemetry(device.id);
    const row = [now, device.name];

    for (const field of FIELDS) {
      if (telemetry && telemetry.data && telemetry.data[field] !== undefined) {
        row.push(telemetry.data[field]);
      } else {
        row.push("");
      }
    }

    row.push(device.status);
    rows.push(row);
  }

  if (rows.length > 0) {
    sheet
      .getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length)
      .setValues(rows);
  }
}

// ========== MENU ==========
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("SiliconWit.IO")
    .addItem("Refresh Current Status", "refreshCurrentStatus")
    .addItem("Append to History Log", "appendHistoryLog")
    .addToUi();
}

Click the save icon (or Ctrl+S) and name the project SiliconWit.IO Integration.

Step 4: Configure Your Fields

The FIELDS array at the top of the script determines which telemetry fields are pulled into the sheet. Update it to match your devices:

// For a weather station
const FIELDS = ["temperature", "humidity", "pressure", "wind_speed"];

// For a power monitor
const FIELDS = ["voltage", "current", "power", "energy"];

// For a GPS tracker
const FIELDS = ["latitude", "longitude", "speed"];

Make sure the column headers in your spreadsheet match the fields you configure.

Step 5: Test It

1. Reload the spreadsheet - you should see a SiliconWit.IO menu in the menu bar
2. Click SiliconWit.IO > Refresh Current Status
3. Google will ask you to authorize the script - click through the prompts
4. Your device data should appear in the Current Status sheet

Tip

If the SiliconWit.IO menu does not appear, close and reopen the spreadsheet. The onOpen function runs when the sheet is first opened.

Step 6: Set Up Automatic Updates

1. In the Apps Script editor, click the clock icon (Triggers) in the left sidebar
2. Click + Add Trigger
3. Configure the first trigger:
   • Function: refreshCurrentStatus
   • Event source: Time-driven
   • Type: Hour timer
   • Interval: Every hour
4. Click Save
5. Add a second trigger:
   • Function: appendHistoryLog
   • Event source: Time-driven
   • Type: Hour timer
   • Interval: Every hour
6. Click Save

Both functions will now run automatically every hour.

Step 7: Add Charts (Optional)

With data flowing into the History Log sheet, you can create charts:

1. Select the data range in the History Log sheet
2. Go to Insert > Chart
3. Choose a line chart
4. Set the X-axis to the Timestamp column
5. Set the series to your telemetry fields (e.g. Temperature, Humidity)

The chart updates automatically as new rows are appended.

Step 8: Add Conditional Formatting (Optional)

Highlight values that need attention:

1. Select the Temperature column in the Current Status sheet
2. Go to Format > Conditional formatting
3. Set a rule: “Greater than 35” with red background
4. Add another rule: “Less than 10” with blue background

Now abnormal readings are instantly visible.

Adapting for Your Use Case

Multiple Sensor Types

If your devices report different fields, you can handle this dynamically:

function refreshCurrentStatusDynamic() {
  const sheet =
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Current Status");
  const devices = getAllDevices();
  const now = new Date().toISOString();

  // Collect all unique fields across all devices
  const allFields = new Set();
  const deviceData = [];

  for (const device of devices) {
    const telemetry = getLatestTelemetry(device.id);
    const data = telemetry && telemetry.data ? telemetry.data : {};
    deviceData.push({ device, data });
    for (const key of Object.keys(data)) {
      if (typeof data[key] === "number") {
        allFields.add(key);
      }
    }
  }

  const fields = Array.from(allFields).sort();

  // Write headers
  const headers = ["Device Name", "Status", "Last Seen", ...fields, "Updated"];
  sheet.clear();
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  // Write data
  const rows = deviceData.map(({ device, data }) => {
    const row = [device.name, device.status, device.last_seen || "Never"];
    for (const field of fields) {
      row.push(data[field] !== undefined ? data[field] : "");
    }
    row.push(now);
    return row;
  });

  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
  }
}

Limiting History Log Size

Google Sheets has a limit of about 10 million cells. To prevent the History Log from growing too large, add a cleanup function:

function trimHistoryLog() {
  const sheet =
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("History Log");
  const maxRows = 10000; // Keep the most recent 10,000 rows
  const currentRows = sheet.getLastRow();

  if (currentRows > maxRows + 1) {
    // +1 for header
    const rowsToDelete = currentRows - maxRows - 1;
    sheet.deleteRows(2, rowsToDelete);
  }
}

Add a daily trigger for trimHistoryLog to keep the sheet manageable.

Troubleshooting

Problem	Cause	Solution
”API error: Unauthorized”	Invalid or expired API key	Create a new key from Dashboard > Settings > API Keys
”API error: Forbidden”	Free plan does not include API access	Upgrade to Starter or above
No data appears	Devices have no telemetry	Check that your devices are sending data
Menu does not appear	Script not saved or sheet not reloaded	Save the script and reopen the spreadsheet
Trigger not running	Authorization revoked	Open the script editor and re-authorize

Summary

In this tutorial, you connected Google Sheets to the SiliconWit.IO Public API and built:

• A Current Status view showing the latest reading from every device
• A History Log that grows over time with hourly snapshots
• Automatic updates via time-driven triggers
• Optional charts and conditional formatting for visual analysis

This same approach works with any spreadsheet tool that supports scripting, including Microsoft Excel with Office Scripts or LibreOffice with macros.

Next Steps

• Automated Reports with Python - Build custom reports with the API
• Public API Reference - All available endpoints
• Alert Integrations - Send alerts to Discord, Slack, Telegram