Health -> GDocs apps script

code.gs

// example sheet: https://docs.google.com/spreadsheets/d/18t70uIadx4muvnrMEzXDi2SeTwJsRs7QvdNq4k0v6mE/edit?usp=sharing

// generate a random string here just in case
const API_KEY = "";

/**
 * Entry point for POST requests
 */
function doPost(e) {
  try {
    if (!e.postData || !e.postData.contents) {
      return jsonResponse({ error: "No POST body" }, 400);
    }

    const payload = JSON.parse(e.postData.contents);

    /*
      Expected payload:
      {
        "data": {
          "dates": ["2025-01-10"],
          "steps": [500],
          "kcal": [500],
          "distance": [1],
        }
        "api_key": ""
      }
    */

    validatePayload(payload);
    const entries = normalizeData(payload.data);
    upsertRows(entries);

    return jsonResponse({ status: "ok" });

  } catch (err) {
    return jsonResponse(
      { error: err.message }
    );
  }
}

function test() {
  upsertRows(
    [
      {
        "date": "2025-06-04",
        "steps": 800,
        "distance": 1.9,
        "kcal": 700
      },
      {
        "date": "2025-06-05",
        "steps": 400,
        "distance": 1.4,
        "kcal": 500
      }
    ]
  )
}

/**
 * Get entry to get back the latest date
 */
function doGet() {
  const sheet = SpreadsheetApp.getActive()
    .getSheetByName("raw_data");

  const data = sheet.getDataRange().getValues();
  let lastDate = null;

  for (let i = 1; i < data.length; i++) {
    if (data[0][5] === "last_written_date") {
      lastDate = data[0][6];
    }
  }

  return jsonResponse({
    last_written_date: lastDate
  });
}


/**
 * Insert or update row by date
 */
function upsertRows(jsonData) {
  const sheet = SpreadsheetApp.getActive()
    .getSheetByName("raw_data");

  if (!sheet) {
    throw new Error("Sheet 'raw_data' not found");
  }

  const values = sheet.getDataRange().getValues();
  const header = values[0];
  const dateCol = header.indexOf("date");
  const stepsCol = header.indexOf("steps");
  const distCol = header.indexOf("distance");
  const kcalCol = header.indexOf("kcal");

  if (dateCol === -1) {
    throw new Error("Missing required columns");
  }

  const rowIndexByDate = new Map();
  for (let i = 1; i < values.length; i++) {
    rowIndexByDate.set(formatDate(values[i][dateCol]), i);
  }

  // Track new rows to append
  const rowsToAppend = [];

  for (const entry of jsonData) {
    const { date, steps, distance, kcal } = entry;

    if (rowIndexByDate.has(date)) {
      // Update existing row in memory
      const rowIdx = rowIndexByDate.get(date);
      values[rowIdx][stepsCol] = steps;
      values[rowIdx][distCol] = distance;
      values[rowIdx][kcalCol] = kcal;
    } else {
      // Prepare new row
      const newRow = new Array(header.length).fill("");
      newRow[dateCol] = date;
      newRow[stepsCol] = steps;
      newRow[distCol] = distance;
      newRow[kcalCol] = kcal;
      rowsToAppend.push(newRow);
    }
  }

  // Write updated existing rows back (single write)
  if (values.length > 1) {
    sheet
      .getRange(1, 1, values.length, values[0].length)
      .setValues(values);
  }

  // Append new rows (single append)
  if (rowsToAppend.length > 0) {
    sheet
      .getRange(
        sheet.getLastRow() + 1,
        1,
        rowsToAppend.length,
        rowsToAppend[0].length
      )
      .setValues(rowsToAppend);
  }

  sheet
  .getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn())
  .sort({ column: dateCol + 1, ascending: true });


  const latestDate = getLatestDate()

  if (values[0][5] === "last_written_date") {
    sheet.getRange(0+1, 6+1).setValue(latestDate);
  }
}

function getLatestDate() {
  const sheet = SpreadsheetApp.getActive()
    .getSheetByName("raw_data");
  const data = sheet.getDataRange().getValues();
  const dateCol = data[0].indexOf("date"); // header row

  let latestDate = null;

  for (let i = 1; i < data.length; i++) {
    const cellValue = data[i][dateCol];
    if (cellValue instanceof Date) {
      if (!latestDate || cellValue > latestDate) {
        latestDate = cellValue;
      }
    }
  }

  // Optional: format as yyyy-mm-dd
  if (latestDate) {
    return formatDate(latestDate)
  }
}

/**
 * Basic validation
 */
function validatePayload(p) {
  if (p.api_key !== API_KEY) {
    throw new Error("Unauthorized");
  }

  if (!p.data || typeof p.data !== "object") {
    throw new Error("Missing data object");
  }

  const { dates, steps, distance, kcal } = p.data;

  if (
    !Array.isArray(dates) ||
    !Array.isArray(steps) ||
    !Array.isArray(distance) ||
    !Array.isArray(kcal)
  ) {
    throw new Error("dates, steps, distance, kcal must be arrays");
  }

  const len = dates.length;

  if (len === 0) {
    throw new Error("Data arrays are empty");
  }

  if (
    steps.length !== len ||
    distance.length !== len ||
    kcal.length !== len
  ) {
    throw new Error("Data arrays must have equal length");
  }
}

function normalizeData(data) {
  const { dates, steps, distance, kcal } = data;

  const result = [];

  for (let i = 0; i < dates.length; i++) {
    result.push({
      date: dates[i],
      steps: steps[i],
      distance: distance[i],
      kcal: kcal[i]
    });
  }

  return result;
}


/**
 * JSON response helper
 */
function jsonResponse(obj) {
  return ContentService
    .createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}

function formatDate(value) {
  if (Object.prototype.toString.call(value) === "[object Date]") {
    return Utilities.formatDate(
      value,
      Session.getScriptTimeZone(),
      "yyyy-MM-dd"
    );
  }
  return value;
}