Google Sheets Setup – Clipping Data

What you’ll get

When your team taps Finish in the Clipping App, a new row is added to your Google Sheet (one row per day: Date | HOC | 1–18 | PG).

Each club should complete this once to connect their own Google Sheet.

Step 1 — Make or open your Google Sheet

Step 2 — Add the Apps Script

/** Set these once for your club */
const SPREADSHEET_ID = 'PUT_YOUR_SHEET_ID_HERE';   // e.g. 1Abc... from your sheet URL
const SHEET_NAME     = 'Clipping Data';

/**
 * Webhook for Clipping App → Google Sheets (wide daily layout)
 * Layout: Date | HOC | 1 | 2 | … | 18 | PG   (values are ml/m²)
 */
function doPost(e) {
  if (!e || !e.postData || !e.postData.contents) {
    return ContentService.createTextOutput('No payload');
  }

  const body = JSON.parse(e.postData.contents);
  const rows = Array.isArray(body.rows) ? body.rows : [body];

  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sh = ensureWideSheet_(ss);

  const map = buildDailyMap_(rows);
  Object.keys(map).forEach(dateKey => {
    const { hoc, holes } = map[dateKey];
    upsertDailyRow_(sh, dateKey, hoc, holes);
  });

  ensureConditionalFormats_(sh);
  return ContentService.createTextOutput('OK');
}

/* ---------- helpers ---------- */

const HOLES  = ['1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','PG'];
const HEADER = ['Date','HOC'].concat(HOLES);

function ensureWideSheet_(ss) {
  let sh = ss.getSheetByName(SHEET_NAME);
  if (!sh) sh = ss.insertSheet(SHEET_NAME);

  // If the sheet is brand new (or empty), set header once.
  const lastRow = sh.getLastRow();
  const lastCol = sh.getLastColumn();
  if (lastRow === 0 || (lastRow === 1 && lastCol === 0)) {
    sh.getRange(1,1,1,HEADER.length).setValues([HEADER]).setFontWeight('bold');
    sh.setFrozenRows(1);
  } else if (lastRow === 1) {
    // Row 1 exists but might be empty/partial; fill missing cells only (no clearing).
    const existing = sh.getRange(1,1,1,Math.max(HEADER.length, sh.getLastColumn())).getValues()[0];
    const toWrite = HEADER.map((h, i) => existing[i] ? existing[i] : h);
    sh.getRange(1,1,1,HEADER.length).setValues([toWrite]).setFontWeight('bold');
    sh.setFrozenRows(1);
  }
  return sh;
}

function buildDailyMap_(rows) {
  const byDate = {};
  rows.forEach(r => {
    const dateStr = (r.date || new Date());
    const date = (typeof dateStr === 'string' && dateStr.includes('-'))
      ? dateStr
      : Utilities.formatDate(new Date(dateStr), Session.getScriptTimeZone(), 'yyyy-MM-dd');

    const hole = String(r.hole || '').toUpperCase().replace(/^HOLE\s*/,'');
    if (!HOLES.includes(hole)) return;

    let mlpm2 = Number(r.mlPerM2);
    if (!mlpm2) {
      const litres = Number(r.litres || 0);
      const m2     = Number(r.greenSize || 0);
      mlpm2 = (m2 > 0) ? Math.round((litres * 1000) / m2) : 0;
    }

    if (!byDate[date]) byDate[date] = { hoc: null, holes: {} };
    if (r.hoc || r.heightOfCut) {
      const v = String(r.hoc || r.heightOfCut);
      if (v !== '') byDate[date].hoc = v;
    }
    byDate[date].holes[hole] = mlpm2;
  });
  return byDate;
}

function upsertDailyRow_(sh, dateKey, hoc, holesObj) {
  const values = sh.getDataRange().getValues();
  let rowIndex = -1;

  // Find existing row for date
  for (let r = 2; r <= values.length; r++) {
    if (String(values[r-1][0]) === dateKey) { rowIndex = r; break; }
  }

  // If not found, append one new row
  if (rowIndex === -1) {
    rowIndex = sh.getLastRow() + 1;
    // Ensure row has the correct width
    if (sh.getLastColumn() < HEADER.length) sh.insertColumnsAfter(sh.getLastColumn(), HEADER.length - sh.getLastColumn());
    sh.getRange(rowIndex, 1, 1, HEADER.length).setValues([new Array(HEADER.length).fill('')]);
    sh.getRange(rowIndex, 1).setValue(dateKey);
  }

  if (hoc) sh.getRange(rowIndex, 2).setValue(hoc);

  HOLES.forEach((h, i) => {
    const col = 3 + i;
    const v = holesObj[h];
    if (v != null) sh.getRange(rowIndex, col).setValue(Number(v) || 0);
  });

  // Sort by Date desc (keep header frozen)
  const lastRow = sh.getLastRow();
  if (lastRow > 2) {
    sh.getRange(2,1,lastRow-1,HEADER.length).sort({ column: 1, ascending: false });
  }
}

function ensureConditionalFormats_(sh) {
  const rules = sh.getConditionalFormatRules();
  const lastCol = 2 + HOLES.length;
  const range = sh.getRange(2, 3, Math.max(1, sh.getMaxRows()-1), lastCol-2);

  if (!rules.length) {
    const ruleLow = SpreadsheetApp.newConditionalFormatRule()
      .whenNumberLessThan(15)
      .setBackground('#C6EFCE').setFontColor('#006100')
      .setRanges([range]).build();

    const ruleMid = SpreadsheetApp.newConditionalFormatRule()
      .whenNumberBetween(15, 30)
      .setBackground('#FFEB9C').setFontColor('#7F6000')
      .setRanges([range]).build();

    const ruleHigh = SpreadsheetApp.newConditionalFormatRule()
      .whenNumberGreaterThan(30)
      .setBackground('#FFC7CE').setFontColor('#9C0006')
      .setRanges([range]).build();

    sh.setConditionalFormatRules([ruleLow, ruleMid, ruleHigh]);
  }
}

Step 3 — Deploy the Web App (Webhook)

Step 4 — Test

1. Enter sample litres / green size for a couple of holes in the Clipping App.
2. Tap Finish.
3. In your sheet’s Clipping Data tab, you should see one row for today (upserts if you submit again the same day).

Heads up: If you edit header titles, the script will not clear the sheet; keep the headers/order for best results.