Keep one Sheets chart up to date

Zone,Ready,Capacity
North bed,4,6
Courtyard,1,4
Roof beds,5,8

/** @OnlyCurrentDoc */
/**
 * @type {{
 *   spreadsheetId: string,
 *   sheetId: number|null,
 *   dataRange: string,
 *   legendPosition: string,
 *   confirmedChartId: number|null
 * }}
 */
const GARDEN_CHART = {
  spreadsheetId: 'PASTE_SPREADSHEET_ID',
  sheetId: null, // Replace with the numeric gid from the practice sheet's URL; 0 is valid.
  dataRange: 'A1:C4', // The lesson owns this exact input rectangle.
  legendPosition: 'bottom', // The exercise changes this to 'right', then restores 'bottom'.
  confirmedChartId: null, // Set only after inspecting the newly inserted chart.
};
const GARDEN_CHART_RECORD_KEY = 'gardenChart.v1';

/**
 * @typedef {{
 *   spreadsheet: SpreadsheetApp.Spreadsheet,
 *   sheet: SpreadsheetApp.Sheet,
 *   range: SpreadsheetApp.Range,
 *   properties: PropertiesService.Properties
 * }} GardenChartContext
 */
/**
 * @typedef {{
 *   version: number,
 *   spreadsheetId: string,
 *   sheetId: number|null,
 *   dataRange: string,
 *   status: 'insert_pending'|'ready',
 *   beforeChartIds: number[],
 *   chartId: number|null
 * }} GardenChartRecord
 */

function createGardenChart() {
  withGardenChartLock_((context) => {
    const record = readGardenChartRecord_(context);
    const charts = getGardenCharts_(context.sheet);
    if (record !== null) {
      if (record.status === 'insert_pending') {
        throw new Error('An insertion is pending. Inspect and record its chart; do not insert again.');
      }
      requireRecordedGardenChart_(context, record, charts);
      console.log('No insertion: chart ID ' + record.chartId + ' is already recorded.');
      return;
    }
    requireGardenChartData_(context.range);
    if (charts.length >= 10) throw new Error('Use a practice sheet with fewer than ten charts.');
    if (charts.some((chart) => {
      const position = chart.getContainerInfo();
      return position.getAnchorRow() === 1 && position.getAnchorColumn() === 5;
    })) {
      throw new Error('A chart is already anchored at E1. Inspect it; this script will not replace it.');
    }
    const chart = context.sheet.newChart()
      .setChartType(Charts.ChartType.COLUMN)
      .addRange(context.range)
      .setNumHeaders(1)
      .setTransposeRowsAndColumns(false)
      .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_ROWS)
      .setOption('useFirstColumnAsDomain', true)
      .setOption('title', 'Garden readiness')
      .setOption('hAxis', { title: 'Garden zone' })
      .setOption('vAxis', { title: 'Planters', minValue: 0, maxValue: 8 })
      .setOption('colors', ['#188038', '#1a73e8'])
      .setOption('isStacked', false)
      .setOption('legend', { position: GARDEN_CHART.legendPosition })
      .setOption('width', 600)
      .setOption('height', 360)
      .setPosition(1, 5, 0, 0)
      .build();
    // A built, uninserted chart has no spreadsheet chart ID to record.
    const pending = {
      version: 1,
      spreadsheetId: GARDEN_CHART.spreadsheetId,
      sheetId: GARDEN_CHART.sheetId,
      dataRange: GARDEN_CHART.dataRange,
      status: 'insert_pending',
      beforeChartIds: charts.map((existing) => existing.getChartId()),
      chartId: null,
    };
    context.properties.setProperty(GARDEN_CHART_RECORD_KEY, JSON.stringify(pending));
    // Do not delete the pending record if insertion, flush, or logging fails.
    context.sheet.insertChart(chart);
    SpreadsheetApp.flush();
    console.log('Insertion call returned. Run inspectGardenChart, then record the actual chart ID.');
  });
}

function inspectGardenChart() {
  withGardenChartLock_((context) => {
    const record = readGardenChartRecord_(context);
    const charts = getGardenCharts_(context.sheet);
    console.log(JSON.stringify({
      spreadsheetId: context.spreadsheet.getId(),
      sheetId: context.sheet.getSheetId(),
      sheetName: context.sheet.getName(),
      dataRange: GARDEN_CHART.dataRange,
      values: context.range.getValues(),
      record,
      requestedLegend: GARDEN_CHART.legendPosition,
      charts: charts.map((chart) => ({
        ...describeGardenChart_(chart),
        newSinceIntent: record !== null && !record.beforeChartIds.includes(requireGardenChartId_(chart)),
        matchesLessonShape: matchesGardenChart_(context, chart),
      })),
    }, null, 2));
  });
}

function recordGardenChart() {
  withGardenChartLock_((context) => {
    const confirmedId = GARDEN_CHART.confirmedChartId;
    if (typeof confirmedId !== 'number' || !Number.isSafeInteger(confirmedId)) {
      throw new Error('Inspect the output, then set confirmedChartId to its numeric chart ID.');
    }
    const record = readGardenChartRecord_(context);
    if (record === null) throw new Error('No insertion intent exists. Do not adopt an unrelated chart.');
    const charts = getGardenCharts_(context.sheet);
    if (record.status === 'ready') {
      if (record.chartId !== confirmedId) throw new Error('The recorded chart ID cannot be replaced.');
      requireRecordedGardenChart_(context, record, charts);
      console.log('No change: this chart ID is already recorded.');
      return;
    }
    const newCharts = charts.filter((chart) => !record.beforeChartIds.includes(requireGardenChartId_(chart)));
    const originalChartsRemain = record.beforeChartIds.every((id) =>
      charts.some((chart) => chart.getChartId() === id));
    if (!originalChartsRemain || newCharts.length !== 1 || newCharts[0].getChartId() !== confirmedId) {
      throw new Error('Expected exactly one new chart and every original chart. Stop and inspect; do not guess.');
    }
    if (!matchesGardenChart_(context, newCharts[0])) {
      throw new Error('The confirmed chart does not have the lesson range, type, headers, and E1 placement.');
    }
    requireGardenChartData_(context.range);
    record.chartId = confirmedId;
    record.status = 'ready';
    context.properties.setProperty(GARDEN_CHART_RECORD_KEY, JSON.stringify(record));
    console.log('Recorded chart ID ' + confirmedId + '. Reset confirmedChartId to null in Code.gs.');
  });
}

function updateGardenChart() {
  withGardenChartLock_((context) => {
    const record = readGardenChartRecord_(context);
    if (record === null || record.status !== 'ready') {
      throw new Error('Inspect and record the inserted chart before updating it.');
    }
    const chart = requireRecordedGardenChart_(context, record, getGardenCharts_(context.sheet));
    requireGardenChartData_(context.range);
    const changedChart = chart.modify()
      .setOption('legend', { position: GARDEN_CHART.legendPosition })
      .build();
    if (changedChart.getChartId() !== record.chartId) {
      throw new Error('The rebuilt chart did not retain the recorded ID. No update was submitted.');
    }
    // Updating an existing chart is not inserting another chart.
    context.sheet.updateChart(changedChart);
    SpreadsheetApp.flush();
    const observed = requireRecordedGardenChart_(context, record, getGardenCharts_(context.sheet));
    console.log(JSON.stringify({ requestedLegend: GARDEN_CHART.legendPosition, observed: describeGardenChart_(observed) }, null, 2));
  });
}

/**
 * @param {function(GardenChartContext): void} action
 * @returns {void}
 */
function withGardenChartLock_(action) {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) throw new Error('Another lesson run holds the lock. Let it finish, then inspect.');
  try {
    return action(getGardenChartContext_());
  } finally {
    lock.releaseLock();
  }
}

/**
 * @returns {GardenChartContext}
 */
function getGardenChartContext_() {
  const sheetId = GARDEN_CHART.sheetId;
  if (GARDEN_CHART.spreadsheetId === 'PASTE_SPREADSHEET_ID' ||
      !/^[A-Za-z0-9_-]+$/.test(GARDEN_CHART.spreadsheetId) ||
      typeof sheetId !== 'number' || !Number.isSafeInteger(sheetId) || sheetId < 0) {
    throw new Error('Configure the practice spreadsheet ID and numeric sheet ID before running.');
  }
  if (GARDEN_CHART.dataRange !== 'A1:C4' ||
      !['bottom', 'right'].includes(GARDEN_CHART.legendPosition)) {
    throw new Error('Keep dataRange A1:C4 and use legendPosition bottom or right.');
  }
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  if (spreadsheet === null || spreadsheet.getId() !== GARDEN_CHART.spreadsheetId) {
    throw new Error('Run from the Apps Script project bound to the configured practice spreadsheet.');
  }
  const sheet = spreadsheet.getSheetById(sheetId);
  if (sheet === null) throw new Error('The configured practice sheet is missing. Do not choose another by position.');
  if (sheet.getMaxRows() < 4 || sheet.getMaxColumns() < 5) {
    throw new Error('The practice sheet must contain A1:C4 and anchor cell E1.');
  }
  return {
    spreadsheet,
    sheet,
    range: sheet.getRange(GARDEN_CHART.dataRange),
    properties: PropertiesService.getScriptProperties(),
  };
}

/**
 * @param {SpreadsheetApp.Range} range
 * @returns {void}
 */
function requireGardenChartData_(range) {
  if (range.isPartOfMerge()) throw new Error('Use twelve unmerged cells in A1:C4.');
  if (range.getFormulas().some((row) => row.some((formula) => formula !== ''))) {
    throw new Error('Use the supplied plain values, not formulas, in A1:C4.');
  }
  const values = range.getValues();
  const headers = ['Zone', 'Ready', 'Capacity'];
  if (!headers.every((header, column) => values[0][column] === header)) {
    throw new Error('A1:C1 must contain Zone, Ready, Capacity in that order.');
  }
  const labels = new Set();
  for (let row = 1; row < 4; row += 1) {
    const [zone, ready, capacity] = values[row];
    if (typeof zone !== 'string' || zone.trim() === '' || zone !== zone.trim() || labels.has(zone)) {
      throw new Error('Use three distinct nonempty zone labels without surrounding spaces.');
    }
    if (typeof ready !== 'number' || typeof capacity !== 'number' ||
        !Number.isInteger(ready) || !Number.isInteger(capacity) ||
        ready < 0 || capacity < 1 || ready > capacity || capacity > 8) {
      throw new Error('Use numeric whole counts: 0 <= Ready <= Capacity <= 8, with Capacity at least 1.');
    }
    labels.add(zone);
  }
}

/**
 * @param {GardenChartContext} context
 * @returns {GardenChartRecord|null}
 */
function readGardenChartRecord_(context) {
  const raw = context.properties.getProperty(GARDEN_CHART_RECORD_KEY);
  if (raw === null) return null;
  /** @type {GardenChartRecord} */
  const record = JSON.parse(raw);
  if (record === null || typeof record !== 'object' || Array.isArray(record) || record.version !== 1 ||
      record.spreadsheetId !== GARDEN_CHART.spreadsheetId || record.sheetId !== GARDEN_CHART.sheetId ||
      record.dataRange !== GARDEN_CHART.dataRange || !['insert_pending', 'ready'].includes(record.status) ||
      !Array.isArray(record.beforeChartIds) || record.beforeChartIds.length >= 10 ||
      !record.beforeChartIds.every(Number.isSafeInteger) ||
      new Set(record.beforeChartIds).size !== record.beforeChartIds.length ||
      (record.status === 'insert_pending' && record.chartId !== null) ||
      (record.status === 'ready' && (typeof record.chartId !== 'number' ||
        !Number.isSafeInteger(record.chartId) || record.beforeChartIds.includes(record.chartId)))) {
    throw new Error('The saved chart record does not match this lesson target. Preserve it and inspect project settings.');
  }
  return record;
}

/**
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {SpreadsheetApp.EmbeddedChart[]}
 */
function getGardenCharts_(sheet) {
  const charts = sheet.getCharts();
  if (charts.length > 10) throw new Error('This lesson inspects at most ten charts on its target sheet.');
  const ids = charts.map((chart) => chart.getChartId());
  if (!ids.every(Number.isSafeInteger) || new Set(ids).size !== ids.length) {
    throw new Error('Could not read distinct stable chart IDs. Stop and inspect the spreadsheet.');
  }
  return charts;
}

/**
 * @param {GardenChartContext} context
 * @param {SpreadsheetApp.EmbeddedChart} chart
 * @returns {boolean}
 */
function matchesGardenChart_(context, chart) {
  const ranges = chart.getRanges();
  const position = chart.getContainerInfo();
  return ranges.length === 1 && ranges[0].getSheet().getSheetId() === context.sheet.getSheetId() &&
    ranges[0].getA1Notation() === GARDEN_CHART.dataRange &&
    chart.modify().getChartType() === Charts.ChartType.COLUMN && chart.getNumHeaders() === 1 &&
    chart.getTransposeRowsAndColumns() === false &&
    position.getAnchorRow() === 1 && position.getAnchorColumn() === 5 &&
    position.getOffsetX() === 0 && position.getOffsetY() === 0;
}

/**
 * @param {GardenChartContext} context
 * @param {GardenChartRecord} record
 * @param {SpreadsheetApp.EmbeddedChart[]} charts
 * @returns {SpreadsheetApp.EmbeddedChart}
 */
function requireRecordedGardenChart_(context, record, charts) {
  const chart = charts.find((candidate) => candidate.getChartId() === record.chartId);
  if (!chart) throw new Error('The recorded chart is missing from this sheet. Do not create a replacement automatically.');
  if (!matchesGardenChart_(context, chart)) {
    throw new Error('The recorded chart was moved or its data/type/header structure changed. Inspect before updating.');
  }
  return chart;
}

/**
 * @param {SpreadsheetApp.EmbeddedChart} chart
 * @returns {number}
 */
function requireGardenChartId_(chart) {
  const chartId = chart.getChartId();
  if (typeof chartId !== 'number' || !Number.isSafeInteger(chartId)) {
    throw new Error('Could not read a stable chart ID. Stop and inspect the spreadsheet.');
  }
  return chartId;
}

/**
 * @param {SpreadsheetApp.EmbeddedChart} chart
 * @returns {{
 *   chartId: number,
 *   type: string,
 *   ranges: {sheetId: number, a1: string}[],
 *   headers: number,
 *   transposed: boolean,
 *   hiddenDimensions: string,
 *   anchor: {row: number, column: number, x: number, y: number},
 *   title: *,
 *   legend: *,
 *   hAxis: *,
 *   vAxis: *
 * }}
 */
function describeGardenChart_(chart) {
  const position = chart.getContainerInfo();
  const options = chart.getOptions();
  return {
    chartId: requireGardenChartId_(chart),
    type: String(chart.modify().getChartType()),
    ranges: chart.getRanges().map((range) => ({ sheetId: range.getSheet().getSheetId(), a1: range.getA1Notation() })),
    headers: chart.getNumHeaders(),
    transposed: chart.getTransposeRowsAndColumns(),
    hiddenDimensions: String(chart.getHiddenDimensionStrategy()),
    anchor: { row: position.getAnchorRow(), column: position.getAnchorColumn(), x: position.getOffsetX(), y: position.getOffsetY() },
    title: options.get('title'),
    legend: options.get('legend'),
    hAxis: options.get('hAxis'),
    vAxis: options.get('vAxis'),
  };
}

{
  "timeZone": "Etc/UTC",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}