Write API Data to a Google Sheet

Fetching API data is only half the job — the other half is writing it into your sheet in a useful way. This post covers the patterns for mapping API responses to sheet rows, efficiently writing in bulk, and keeping your sheet in sync with external data.

Writing a Single API Response to a Sheet

function writeWeatherToSheet() {
  var apiKey = PropertiesService.getScriptProperties().getProperty("WEATHER_API_KEY");
  var url = "https://api.openweathermap.org/data/2.5/weather?q=New York&appid=" + apiKey + "&units=metric";

  var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  if (response.getResponseCode() !== 200) return;

  var data = JSON.parse(response.getContentText());

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Weather Log")
    || SpreadsheetApp.getActiveSpreadsheet().insertSheet("Weather Log");

  sheet.appendRow([
    new Date(),
    data.name,
    data.main.temp + "°C",
    data.weather[0].description,
    data.main.humidity + "%"
  ]);

  Logger.log("Weather data written.");
}

Bulk Writing API Results with setValues()

For large datasets, always use setValues() instead of appendRow() in a loop — it's much faster:

function writeCRMDealsToSheet() {
  var token = PropertiesService.getScriptProperties().getProperty("CRM_API_TOKEN");

  var response = UrlFetchApp.fetch("https://api.your-crm.com/v1/deals?status=won", {
    headers: { "Authorization": "Bearer " + token },
    muteHttpExceptions: true
  });

  if (response.getResponseCode() !== 200) return;

  var deals = JSON.parse(response.getContentText()).deals;

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("CRM Sync") || ss.insertSheet("CRM Sync");
  sheet.clearContents();

  sheet.appendRow(["ID", "Customer", "Product", "Amount", "Stage", "Close Date"]);

  var rows = deals.map(function(deal) {
    return [
      deal.id,
      deal.contact.name,
      deal.title,
      deal.value,
      deal.stage,
      deal.close_date
    ];
  });

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

  Logger.log("Wrote " + rows.length + " deals from CRM.");
}

Enriching Existing Sheet Data with API Responses

This reads each customer's email domain from the Sales Tracker, calls an enrichment API, and writes the company name back in a new column:

function enrichCustomersFromAPI() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 10).getValues();
  // Columns: [Customer Name, Email, Product, Amount, Region, Sales Rep, Status, Date, -, Company]

  // Ensure Company header exists
  sheet.getRange(1, 10).setValue("Company");

  var apiKey = PropertiesService.getScriptProperties().getProperty("ENRICHMENT_API_KEY");

  data.forEach(function(row, i) {
    var email = row[1];
    var existingCompany = row[9];

    if (!email || existingCompany) return; // Skip if already enriched

    var domain = email.split("@")[1];
    var url = "https://company.clearbit.com/v2/companies/find?domain=" + domain;

    var response = UrlFetchApp.fetch(url, {
      headers: { "Authorization": "Bearer " + apiKey },
      muteHttpExceptions: true
    });

    if (response.getResponseCode() !== 200) {
      sheet.getRange(i + 2, 10).setValue(domain);
      return;
    }

    var company = JSON.parse(response.getContentText());
    sheet.getRange(i + 2, 10).setValue(company.name || domain);
    Logger.log("Enriched: " + row[0] + " → " + company.name);

    Utilities.sleep(300);
  });
}

With the sample data, [email protected] enriches to Tech Ventures, [email protected] to Delta Tech, and so on.

Polling an API on a Schedule and Writing Updates

Combine a time-driven trigger with an API call to keep a sheet live:

function pollAndUpdateDealsSheet() {
  var token = PropertiesService.getScriptProperties().getProperty("CRM_API_TOKEN");

  // Fetch only deals updated in the last hour
  var since = new Date(new Date().getTime() - 60 * 60 * 1000).toISOString();
  var url = "https://api.your-crm.com/v1/deals?updated_since=" + since;

  var response = UrlFetchApp.fetch(url, {
    headers: { "Authorization": "Bearer " + token },
    muteHttpExceptions: true
  });

  if (response.getResponseCode() !== 200) return;

  var deals = JSON.parse(response.getContentText()).deals;

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sales Tracker");
  var existing = sheet.getRange(2, 1, sheet.getLastRow() - 1, 9).getValues();

  deals.forEach(function(deal) {
    var matchRow = existing.findIndex(function(r) { return r[1] === deal.contact.email; });

    if (matchRow !== -1) {
      // Update the Status column for the matched row
      sheet.getRange(matchRow + 2, 7).setValue(deal.stage === "won" ? "Closed" : "In Progress");
      Logger.log("Updated status for: " + deal.contact.name);
    }
  });

  Logger.log("Poll complete. " + deals.length + " deals checked.");
}

Set this to run hourly with a time-based trigger to keep the Sales Tracker automatically in sync with your CRM.