Health -> GDocs apps script

code.gs

// example sheet: https://docs.google.com/spreadsheets/d/18t70uIadx4muvnrMEzXDi2SeTwJsRs7QvdNq4k0v6mE/edit?usp=sharing
// shortcut: https://www.icloud.com/shortcuts/a0b98e0402bd44448ae2e4d8f69bc09d

// 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;
}

The wannabe post on reddit:

Some of you was interested in my shortcut, so I share it here!

It has 2 part that you need to configure:
- sheets
- shortcut

Lets start with the sheet:
https://gist.github.com/tg44/9ed59a19847a4b6bc93ce693f3192aa0

Open the gist, there is an example sheet in the first line, you can copy that to your drive.

After that, you need to go to Extensions -> apps scripts, and copy the appearing code file the code in the gist, and add a (random generated) string between the apostrophs in the api key variable in the 5th line (this can be anything, its just adds one more layer of security).

You need to save the script and deploy it with the blue button on top of the screen. You need to create a new deployment with "web" (I don't know the english name bcs for some reason my google is localized), and you need to set the executor as "me", and the access part to anyone. You can follow this blogpost: https://medium.com/@chennupativardan/how-to-create-an-api-call-using-google-web-app-apps-script-734b468ddcdd

The script itself has two endpoints, one will tell us what was the last time you run the script, and one that can get the data and write it into the sheet itself. I don't really made the code "user friendly", it basically just 20 mins of vibecoding, I think if you want to, you can chatgpt it to modify it, if you want to save other metrics.

From this part, you will need the web app url, and the api key you set.

The shortcut:
It is linked in the gist (first link). You need to modify the first two blocks, one with the api key, and one with the app url. Then you can run it.

It basically, calls the sheet to get the last run date, goes back 2 days in time, queries the daily aggregated step, distance, and kcal data, and send it back to the sheet.

If you have any questions, you can ask me here, or under the gist.

Also; If you are refactoring the script code to be more flexible, I would be happy to share it here!