WooCommerce Google Sheets Script Generator

/**
 * WooCommerce orders -> Google Sheets.
 * 1. In Google Sheets: Extensions -> Apps Script, delete the placeholder code, paste this file.
 * 2. Project Settings (gear icon) -> Script Properties, add three properties:
 *      WC_URL      https://yourstore.com
 *      WC_KEY      ck_...  (a WooCommerce REST API key, Read permission is enough)
 *      WC_SECRET   cs_...
 * 3. Run syncWooCommerceOrders once by itself (function dropdown at the top) to authorize it and test it.
 * 4. Run installTrigger once to put it on a schedule.
 */

function syncWooCommerceOrders() {
  var props = PropertiesService.getScriptProperties();
  var base = props.getProperty("WC_URL");
  var key = props.getProperty("WC_KEY");
  var secret = props.getProperty("WC_SECRET");
  if (!base || !key || !secret) throw new Error("Set WC_URL, WC_KEY and WC_SECRET in Script Properties first.");

  var auth = "Basic " + Utilities.base64Encode(key + ":" + secret);
  var headers = ["ID","Number","Status","Date","Total","Currency","Customer email","Billing name"];
  var all = [];
  for (var page = 1; page <= 50; page++) {
    var params = { per_page: "100", page: String(page) };
    params.status = "processing,completed";
    var qs = Object.keys(params)
      .map(function (k) { return k + "=" + encodeURIComponent(params[k]); })
      .join("&");
    var res = UrlFetchApp.fetch(base + "/wp-json/wc/v3/orders?" + qs, { headers: { Authorization: auth }, muteHttpExceptions: true });
    if (res.getResponseCode() !== 200) throw new Error("WooCommerce answered with HTTP " + res.getResponseCode() + ": " + res.getContentText());
    var batch = JSON.parse(res.getContentText());
    all = all.concat(batch);
    if (batch.length < 100) break;
  }

  var rows = toRows(all);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("WooCommerce Orders") || ss.insertSheet("WooCommerce Orders");
  sheet.clearContents();
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  if (rows.length) sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

function toRows(items) {
  return items.map(function (o) {
    return [o.id, o.number, o.status, o.date_created, o.total, o.currency, (o.billing && o.billing.email) || "", ((o.billing && o.billing.first_name) || "") + " " + ((o.billing && o.billing.last_name) || "")];
  });
}

function installTrigger() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === "syncWooCommerceOrders") ScriptApp.deleteTrigger(t);
  });
  var b = ScriptApp.newTrigger("syncWooCommerceOrders").timeBased();
  b.everyHours(1).create();
}