Google Sheets Integration

Import mining data directly into Google Sheets for analysis, reporting, and visualization.

Method 1: IMPORTDATA Function

The simplest way to import data uses Google Sheets' built-in IMPORTDATA function with CSV format.

Basic Import

=IMPORTDATA("https://api.canetoad.ai/api/v1/companies?format=csv&per_page=100&api_key=ct_your_token")

Using api_key in the URL exposes your token in the sheet. Consider using Apps Script for sensitive data.

Filtered Import

=IMPORTDATA("https://api.canetoad.ai/api/v1/companies?format=csv&sector=Materials&per_page=100&api_key=ct_your_token")

Tenements Import

=IMPORTDATA("https://api.canetoad.ai/api/v1/tenements?format=csv&state=WA&per_page=100&api_key=ct_your_token")

Method 2: Apps Script (Recommended)

For more control and security, use Google Apps Script.

Setup

Open Script Editor

In your Google Sheet, go to Extensions → Apps Script.

Add the Script

Replace the contents with:

// Store your API key in Script Properties (more secure)
// Go to Project Settings (gear icon) → Script Properties → Add
// Key: CANETOAD_API_KEY, Value: ct_your_token

function getApiKey() {
  return PropertiesService.getScriptProperties().getProperty('CANETOAD_API_KEY');
}

function fetchCaneToadData(endpoint, params = {}) {
  const apiKey = getApiKey();
  if (!apiKey) {
    throw new Error('API key not configured. Add CANETOAD_API_KEY to Script Properties.');
  }
  
  const queryString = Object.entries(params)
    .map(([k, v]) => `${k}=${encodeURIComponent(v)}`)
    .join('&');
  
  const url = `https://api.canetoad.ai/api/v1/${endpoint}?${queryString}`;
  
  const response = UrlFetchApp.fetch(url, {
    headers: { 'Authorization': 'Bearer ' + apiKey },
    muteHttpExceptions: true
  });
  
  if (response.getResponseCode() !== 200) {
    const error = JSON.parse(response.getContentText());
    throw new Error(`API Error: ${error.error.message}`);
  }
  
  return JSON.parse(response.getContentText());
}

function importCompanies() {
  const { data } = fetchCaneToadData('companies', { per_page: 100 });
  writeToSheet('Companies', data);
}

function importTenements() {
  const { data } = fetchCaneToadData('tenements', { per_page: 100, state: 'WA' });
  writeToSheet('Tenements', data);
}

function importReports() {
  const { data } = fetchCaneToadData('reports', { per_page: 50 });
  writeToSheet('Reports', data);
}

function writeToSheet(sheetName, data) {
  if (!data || data.length === 0) {
    Logger.log('No data to write');
    return;
  }
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(sheetName);
  
  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
  }
  
  // Clear existing data
  sheet.clear();
  
  // Write headers
  const headers = Object.keys(data[0]);
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
  
  // Write data
  const rows = data.map(row => headers.map(h => row[h] ?? ''));
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
  
  // Auto-resize columns
  headers.forEach((_, i) => sheet.autoResizeColumn(i + 1));
  
  Logger.log(`Wrote ${rows.length} rows to ${sheetName}`);
}

// Menu for easy access
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('CaneToad API')
    .addItem('Import Companies', 'importCompanies')
    .addItem('Import Tenements', 'importTenements')
    .addItem('Import Reports', 'importReports')
    .addToUi();
}

Configure API Key

Click the gear icon (Project Settings)
Scroll to Script Properties
Click Add Script Property
Set Name: CANETOAD_API_KEY
Set Value: Your API token (e.g., ct_xxxxx)

Run the Script

Reload your spreadsheet
A new CaneToad API menu will appear
Click CaneToad API → Import Companies
Authorize the script when prompted

Method 3: Scheduled Refresh

Set up automatic data refresh using time-driven triggers.

Add a Trigger

function setupDailyRefresh() {
  // Delete existing triggers
  ScriptApp.getProjectTriggers().forEach(trigger => {
    if (trigger.getHandlerFunction() === 'refreshAllData') {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  
  // Create daily trigger at 6 AM
  ScriptApp.newTrigger('refreshAllData')
    .timeBased()
    .atHour(6)
    .everyDays(1)
    .create();
  
  Logger.log('Daily refresh trigger created');
}

function refreshAllData() {
  try {
    importCompanies();
    importTenements();
    importReports();
    Logger.log('All data refreshed successfully');
  } catch (error) {
    Logger.log('Error refreshing data: ' + error.message);
    // Optionally send email notification
    MailApp.sendEmail(
      Session.getActiveUser().getEmail(),
      'CaneToad API Refresh Failed',
      'Error: ' + error.message
    );
  }
}

To set up: Run setupDailyRefresh() once from the Apps Script editor.

Pagination for Large Datasets

Fetch all pages of data:

async function fetchAllPages(endpoint, params = {}) {
  let allData = [];
  let page = 1;
  
  while (true) {
    const response = fetchCaneToadData(endpoint, { ...params, page, per_page: 100 });
    allData = allData.concat(response.data);
    
    if (page >= response.meta.total_pages) break;
    page++;
    
    // Avoid rate limiting
    Utilities.sleep(500);
  }
  
  return allData;
}

function importAllCompanies() {
  const data = fetchAllPages('companies');
  writeToSheet('All Companies', data);
}

Troubleshooting

Best Practices

Use Apps Script for production dashboards
Store API keys in Script Properties, not in code
Implement error handling and logging
Respect rate limits with appropriate delays
Cache data locally when possible

Google Sheets Integration

IMPORTDATA shows #N/A error

Apps Script authorization failed

Data not refreshing

On this page