Tag: Google Sheets

sheet.getRange("A1").setValue("Hello");
sheet.getRange("A1").setBackground("#000");
sheet.getRange("A1").setFontWeight("bold");
// and so on...

class HeaderComponent extends SheetComponent {
  render() {
    this.context.writeRange("Sheet1!A1", [["Hello"]]);
    this.context.setBackground("Sheet1", "A1", "#000");
  }
}

class SheetContext {
  constructor(spreadsheetId) {
    this.spreadsheetId = spreadsheetId;
    this.valueRanges = [];
    this.requests = [];
  }

  writeRange(rangeA1, values) {
    this.valueRanges.push({ range: rangeA1, values });
  }

  setBackground(sheetName, a1Range, color) {
    const gridRange = this.convertA1ToGridRange(sheetName, a1Range);
    if (!gridRange) return;

    this.requests.push({
      repeatCell: {
        range: gridRange,
        cell: { userEnteredFormat: { backgroundColor: this.hexToRgbColor(color) }},
        fields: "userEnteredFormat.backgroundColor",
      },
    });
  }

  setNumberFormat(sheetName, a1Range, numberFormatPattern) {
    const gridRange = this.convertA1ToGridRange(sheetName, a1Range);
    if (!gridRange) return;

    this.requests.push({
      repeatCell: {
        range: gridRange,
        cell: {
          userEnteredFormat: {
            numberFormat: { type: "NUMBER", pattern: numberFormatPattern },
          },
        },
        fields: "userEnteredFormat.numberFormat",
      },
    });
  }

  setTextFormat(sheetName, a1Range, options) {
    const gridRange = this.convertA1ToGridRange(sheetName, a1Range);
    if (!gridRange) return;

    const textFormat = {};
    if (options.bold !== undefined) textFormat.bold = options.bold;
    if (options.italic !== undefined) textFormat.italic = options.italic;
    if (options.underline !== undefined) textFormat.underline = options.underline;
    if (options.fontSize !== undefined) textFormat.fontSize = options.fontSize;
    if (options.color) {
      textFormat.foregroundColor = this.hexToRgbColor(options.color);
    }

    this.requests.push({
      repeatCell: {
        range: gridRange,
        cell: { userEnteredFormat: { textFormat }},
        fields: "userEnteredFormat.textFormat",
      },
    });
  }

  setAlignment(sheetName, a1Range, options) {
    const gridRange = this.convertA1ToGridRange(sheetName, a1Range);
    if (!gridRange) return;

    const format = {};
    const fields = [];

    if (options.horizontal) {
      format.horizontalAlignment = options.horizontal;
      fields.push("userEnteredFormat.horizontalAlignment");
    }
    if (options.vertical) {
      format.verticalAlignment = options.vertical;
      fields.push("userEnteredFormat.verticalAlignment");
    }

    this.requests.push({
      repeatCell: {
        range: gridRange,
        cell: { userEnteredFormat: format },
        fields: fields.join(","),
      },
    });
  }

  setWrapStrategy(sheetName, a1Range, wrapStrategy) {
    const gridRange = this.convertA1ToGridRange(sheetName, a1Range);
    if (!gridRange) return;

    this.requests.push({
      repeatCell: {
        range: gridRange,
        cell: { userEnteredFormat: { wrapStrategy }},
        fields: "userEnteredFormat.wrapStrategy",
      },
    });
  }

  setBorders(sheetName, a1Range, options) {
    const gridRange = this.convertA1ToGridRange(sheetName, a1Range);
    if (!gridRange) return;

    const border = {
      style: options.style || "SOLID",
      width: options.width || 1,
      color: options.color ? this.hexToRgbColor(options.color) : undefined,
    };

    this.requests.push({
      updateBorders: {
        range: gridRange,
        top: border,
        bottom: border,
        left: border,
        right: border,
        innerHorizontal: border,
        innerVertical: border,
      },
    });
  }

  mergeRange(sheetName, a1Range, mergeType) {
    const gridRange = this.convertA1ToGridRange(sheetName, a1Range);
    if (!gridRange) return;

    this.requests.push({
      mergeCells: {
        range: gridRange,
        mergeType: mergeType || "MERGE_ALL",
      },
    });
  }

  unmergeRange(sheetName, a1Range) {
    const gridRange = this.convertA1ToGridRange(sheetName, a1Range);
    if (!gridRange) return;

    this.requests.push({ unmergeCells: { range: gridRange }});
  }

  autoResizeColumns(sheetName, a1Range) {
    const gridRange = this.convertA1ToGridRange(sheetName, a1Range);
    if (!gridRange) return;

    this.requests.push({
      autoResizeDimensions: {
        dimensions: {
          sheetId: gridRange.sheetId,
          dimension: "COLUMNS",
          startIndex: gridRange.startColumnIndex,
          endIndex: gridRange.endColumnIndex,
        },
      },
    });
  }

  setDataValidationList(sheetName, a1Range, values) {
    const gridRange = this.convertA1ToGridRange(sheetName, a1Range);
    if (!gridRange || values.length === 0) return;

    this.requests.push({
      setDataValidation: {
        range: gridRange,
        rule: {
          condition: {
            type: "ONE_OF_LIST",
            values: values.map(v => ({ userEnteredValue: v })),
          },
          showCustomUi: true,
        },
      },
    });
  }

  addRequest(request) {
    this.requests.push(request);
  }

  commit() {
    if (this.requests.length > 0) {
      Sheets.Spreadsheets.batchUpdate(
        { requests: this.requests },
        this.spreadsheetId
      );
    }

    if (this.valueRanges.length > 0) {
      Sheets.Spreadsheets.Values.batchUpdate(
        { valueInputOption: "USER_ENTERED", data: this.valueRanges },
        this.spreadsheetId
      );
    }
  }

  hexToRgbColor(hex) {
    let clean = hex.replace("#", "");
    if (clean.length === 3) {
      clean = clean.split("").map(ch => ch + ch).join("");
    }
    return {
      red: parseInt(clean.substring(0, 2), 16) / 255,
      green: parseInt(clean.substring(2, 4), 16) / 255,
      blue: parseInt(clean.substring(4, 6), 16) / 255,
    };
  }

  convertA1ToGridRange(sheetName, a1Range) {
    const ss = SpreadsheetApp.openById(this.spreadsheetId);
    const sheet = ss.getSheetByName(sheetName);
    if (!sheet) throw new Error(`Sheet not found: ${sheetName}`);

    const range = sheet.getRange(a1Range);
    return {
      sheetId: sheet.getSheetId(),
      startRowIndex: range.getRow() - 1,
      endRowIndex: range.getRow() - 1 + range.getNumRows(),
      startColumnIndex: range.getColumn() - 1,
      endColumnIndex: range.getColumn() - 1 + range.getNumColumns(),
    };
  }
}

class SheetComponent {
  constructor(context, props) {
    this.context = context;
    this.props = props || {};
  }

  render() {
    // To be overridden
  }

  renderChild(ComponentClass, props) {
    const child = new ComponentClass(this.context, props);
    child.render();
    return child;
  }
}

class StatusPill extends SheetComponent {
  render() {
    const { sheetName, range, status } = this.props;

    const color =
      status === "Done" ? "#10b981" :
      status === "Blocked" ? "#ef4444" :
      "#facc15";

    this.context.setBackground(sheetName, range, color);
    this.context.setTextFormat(sheetName, range, { bold: true, color: "#fff" });
    this.context.setAlignment(sheetName, range, { horizontal: "CENTER" });
  }
}

**
 * Component that writes a big title and optional subtitle at the top.
 */
class TitleComponent extends SheetComponent {

  render() {
    const sheetName = (this.props.sheetName);
    const title = (this.props.title);
    const subtitle = (this.props.subtitle);

    this.context.writeRange(`${sheetName}!A1`, [[title]]);
    this.context.mergeRange(sheetName, "A1:D1", "MERGE_ALL");
    this.context.setBackground(sheetName, "A1:D1", "#0f172a");
    this.context.setTextFormat(sheetName, "A1:D1", {
      bold: true,
      fontSize: 16,
      color: "#ffffff",
    });
    this.context.setAlignment(sheetName, "A1:D1", { horizontal: "CENTER", vertical: "MIDDLE" });

    if (subtitle) {
      this.context.writeRange(`${sheetName}!A2`, [[subtitle]]);
      this.context.mergeRange(sheetName, "A2:D2", "MERGE_ALL");
      this.context.setTextFormat(sheetName, "A2:D2", {
        italic: true,
        color: "#475569",
      });
      this.context.setAlignment(sheetName, "A2:D2", { horizontal: "CENTER", vertical: "MIDDLE" });
    }
  }
}

/**
 * Component that writes a header row with styling.
 */
class HeaderRowComponent extends SheetComponent {
  /**
   * @returns {void}
   */
  render() {
    const sheetName = (this.props.sheetName);
    const headers = (this.props.headers);
    const rowIndex = (this.props.rowIndex);

    const lastColLetter = columnIndexToLetter(headers.length);
    const rangeA1 = `${sheetName}!A${rowIndex}:${lastColLetter}${rowIndex}`;

    this.context.writeRange(rangeA1, [headers]);
    this.context.setBackground(sheetName, `A${rowIndex}:${lastColLetter}${rowIndex}`, "#1e293b");
    this.context.setTextFormat(sheetName, `A${rowIndex}:${lastColLetter}${rowIndex}`, {
      bold: true,
      color: "#ffffff",
    });
    this.context.setAlignment(sheetName, `A${rowIndex}:${lastColLetter}${rowIndex}`, {
      horizontal: "CENTER",
      vertical: "MIDDLE",
    });
  }
}

/**
 * Component that writes a 2D data table starting at a given row.
 */
class DataTableComponent extends SheetComponent {
  /**
   * @returns {void}
   */
  render() {
    const sheetName = (this.props.sheetName);
    const rows = (this.props.rows);
    const startRow = (this.props.startRow);

    if (!rows || rows.length === 0) return;

    const numCols = rows[0].length;
    const lastColLetter = columnIndexToLetter(numCols);
    const endRow = startRow + rows.length - 1;
    const rangeA1 = `${sheetName}!A${startRow}:${lastColLetter}${endRow}`;

    this.context.writeRange(rangeA1, rows);
    this.context.setBorders(sheetName, `A${startRow}:${lastColLetter}${endRow}`, {
      style: "SOLID",
      width: 1,
      color: "#cbd5e1",
    });
    this.context.setAlignment(sheetName, `A${startRow}:${lastColLetter}${endRow}`, {
      vertical: "MIDDLE",
    });
    this.context.setWrapStrategy(sheetName, `A${startRow}:${lastColLetter}${endRow}`, "WRAP");
  }
}

class SummaryRowComponent extends SheetComponent {
  /**
   * @returns {void}
   */
  render() {
    const sheetName = (this.props.sheetName);
    const label = (this.props.label);
    const rowIndex = (this.props.rowIndex);
    const numCols = (this.props.numCols);
    const amountColumnIndex = (this.props.amountColumnIndex);

    const lastColLetter = columnIndexToLetter(numCols);
    const labelCell = `A${rowIndex}`;
    const amountCell = `${columnIndexToLetter(numCols)}${rowIndex}`;
    const labelRangeSingle = `${sheetName}!${labelCell}`;
    const fullRowRange = `A${rowIndex}:${lastColLetter}${rowIndex}`;
    const amountRange = `${sheetName}!${amountCell}`;

    // Write label in a single cell only
    this.context.writeRange(labelRangeSingle, [[label]]);

    // Then merge the label area visually
    this.context.mergeRange(
      sheetName,
      `A${rowIndex}:${columnIndexToLetter(numCols - 1)}${rowIndex}`,
      "MERGE_ALL"
    );

    this.context.setAlignment(sheetName, fullRowRange, {
      horizontal: "RIGHT",
      vertical: "MIDDLE",
    });
    this.context.setBackground(sheetName, fullRowRange, "#e2e8f0");
    this.context.setTextFormat(sheetName, fullRowRange, {
      bold: true,
    });

    const dataStartRow = (this.props.dataStartRow);
    const dataEndRow = (this.props.dataEndRow);
    const amountColLetter = columnIndexToLetter(amountColumnIndex);
    const sumFormula = `=SUM(${amountColLetter}${dataStartRow}:${amountColLetter}${dataEndRow})`;

    this.context.writeRange(amountRange, [[sumFormula]]);
    this.context.setNumberFormat(sheetName, amountCell, "#,##0.00");
  }
}

/**
 * Root dashboard component that composes title, headers, data and summary.
 */
class DemoDashboardComponent extends SheetComponent {

  render() {
    const sheetName = (this.props.sheetName);
    const headers = (this.props.headers);
    const rows = (this.props.rows);

    this.renderChild(TitleComponent, {
      sheetName,
      title: "Mini Apps Script Component Demo",
      subtitle: "Rendered with a single batchUpdate + values.batchUpdate",
    });

    const headerRowIndex = 4;
    this.renderChild(HeaderRowComponent, {
      sheetName,
      headers,
      rowIndex: headerRowIndex,
    });

    const dataStartRow = headerRowIndex + 1;
    this.renderChild(DataTableComponent, {
      sheetName,
      rows,
      startRow: dataStartRow,
    });

    const dataEndRow = dataStartRow + rows.length - 1;
    const summaryRowIndex = dataEndRow + 2;

    this.renderChild(SummaryRowComponent, {
      sheetName,
      label: "Total amount",
      rowIndex: summaryRowIndex,
      numCols: headers.length,
      amountColumnIndex: headers.length,
      dataStartRow,
      dataEndRow,
    });

    const lastColLetter = columnIndexToLetter(headers.length);
    this.context.autoResizeColumns(sheetName, `A1:${lastColLetter}${summaryRowIndex}`);
  }
}

/**
 * Entry point: renders a full demo dashboard using the component system.
 *
 * @returns {void}
 */
function renderDemoDashboard() {
  const ss = SpreadsheetApp.openById("1mRno9TPj1iXGQ_lEqBIy3jOz4tyJm6upsVhklGuG1BQ")
  const spreadsheetId = ss.getId();
  const sheetName = "Demo";
  const headers = ["Project", "Owner", "Hours", "Amount"];
  const rows = [
    ["Surf Schedule App", "Lidia", 6, 750],
    ["Invoice Generator", "Lidia", 4, 520],
    ["ColorMyPie Add-on", "Lidia", 3, 380],
    ["Client Dashboard", "Lidia", 5, 640],
  ];

  renderSheet(spreadsheetId, DemoDashboardComponent, {
    sheetName,
    headers,
    rows,
  });
}