Apple Health to Google Sheets

// HealthAPI to Google Sheets: one row per day in a sheet called "Health".
// Your API key goes in Project Settings > Script properties as HEALTHAPI_KEY.

const API = "https://healthapi.app/api/v1";
const SHEET_NAME = "Health";
const FILL_DAYS = 20;

// Each column: its title, and how to read it from the daily summary.
const COLUMNS = [
  ["Steps", (d) => d.totals.steps],
  ["Active energy (kcal)", (d) => round(d.totals.active_energy_kcal)],
  ["Exercise (min)", (d) => round(d.totals.exercise_time_min)],
  ["Distance (km)", (d) => round(d.totals.distance_m / 1000, 2)],
  ["Resting heart rate (bpm)", (d) => round(d.averages.resting_heart_rate_bpm)],
  ["HRV (ms)", (d) => round(d.averages.hrv_ms)],
  ["Sleep (h)", (d) => d.sleep_hours],
  ["Workouts", (d) => d.workout_count],
  ["Workout time (min)", (d) => d.workout_duration_minutes],
];

// The daily trigger runs this: yesterday, and the day before again in case
// your iPhone synced late.
function addYesterday() {
  writeDay(daysAgo(2));
  writeDay(daysAgo(1));
}

// Run once by hand to fill in the past. One request per day.
function fillPastDays() {
  for (let n = FILL_DAYS; n >= 1; n--) writeDay(daysAgo(n));
}

function writeDay(date) {
  const sheet = getSheet();
  const d = fetchJson("/health/daily-summary?date=" + date);
  const row = [date].concat(COLUMNS.map(([, read]) => blank(read(d))));
  const at = findRow(sheet, date);
  if (at) sheet.getRange(at, 1, 1, row.length).setValues([row]);
  else sheet.appendRow(row);
}

function fetchJson(path) {
  const key = PropertiesService.getScriptProperties().getProperty("HEALTHAPI_KEY");
  if (!key) throw new Error("Add your API key in Project Settings > Script properties as HEALTHAPI_KEY.");
  const res = UrlFetchApp.fetch(API + path, {
    headers: { Authorization: "Bearer " + key },
    muteHttpExceptions: true,
  });
  const body = JSON.parse(res.getContentText() || "{}");
  if (res.getResponseCode() !== 200) {
    const why = typeof body.detail === "string" ? body.detail : res.getContentText();
    throw new Error("HealthAPI " + res.getResponseCode() + ": " + why);
  }
  return body;
}

function getSheet() {
  const book = SpreadsheetApp.getActive();
  let sheet = book.getSheetByName(SHEET_NAME);
  if (!sheet) {
    sheet = book.insertSheet(SHEET_NAME);
    sheet.appendRow(["Date"].concat(COLUMNS.map(([title]) => title)));
    sheet.setFrozenRows(1);
  }
  return sheet;
}

// The row that already holds this date, or 0.
function findRow(sheet, date) {
  const last = sheet.getLastRow();
  if (last < 2) return 0;
  const tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  const dates = sheet.getRange(2, 1, last - 1, 1).getValues();
  for (let i = 0; i < dates.length; i++) {
    const v = dates[i][0];
    const s = v instanceof Date ? Utilities.formatDate(v, tz, "yyyy-MM-dd") : String(v);
    if (s === date) return i + 2;
  }
  return 0;
}

function daysAgo(n) {
  const tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  return Utilities.formatDate(new Date(Date.now() - n * 86400000), tz, "yyyy-MM-dd");
}

function round(v, places) {
  if (v === null || v === undefined) return null;
  const f = Math.pow(10, places || 0);
  return Math.round(v * f) / f;
}

function blank(v) {
  return v === null || v === undefined ? "" : v;
}