Showing Specific Rows and Columns in Google Spreadsheet using Google Apps Script

27 Sep 2022, 10:41

GasTips

This is a sample script for showing the specific rows and columns in Google Spreadsheet using Google Apps Script.

When you export a Google Spreadsheet as a PDF file, you might have a case where you want to export the specific rows and columns in a sheet. In this post, I would like to introduce the sample script for achieving this.

Script

Please copy and paste the following scripts to the script editor of Google Spreadsheet.

This script used Sheets API. So, please enable Sheets API at Advanced Google services.

Class ShowRowsColumns

This is a Class ShowRowsColumns.

/**
 * Show rows and columns by giving the row and column information.
 * @typedef Obj
 * @property {object[]} rows Row numbers you want to show.
 * @property {object[]} columns Column letters you want to show.
 * @property {string} spreadsheetId Spreadsheet ID.
 * @property {number}} sheetId Sheet ID. You can use Sheet ID or Sheet name.
 * @property {string} sheetName Sheet name. You can use Sheet ID or Sheet name.
 */
class ShowRowsColumns {
  /**
   * @param {Obj} params
   * @return {ShowRowsColumns}
   */
  constructor({
    rows = [],
    columns = [],
    spreadsheetId = "",
    sheetId = "",
    sheetName = "",
  }) {
    this.rows = rows;
    this.columns = columns.map(({ start, end }) => ({
      start: this.columnLetterToIndex_(start) + 1,
      end: this.columnLetterToIndex_(end) + 1,
    }));
    this.ssId = spreadsheetId;
    this.sheetId = sheetId;
    this.sheetName = sheetName;
    if (!this.ssId) throw new Error("Please set Spreadsheet ID.");
    if (!this.sheetId.toString() && !this.sheetName)
      throw new Error("Please set sheet ID or sheetName.");
    this.rowCount = 1000;
    this.columnCount = 26;
    this.getSheet_();
  }

  /**
   * Get sheet object.
   * @return {object} Sheet object.
   */
  getSheet_() {
    const { sheets } = Sheets.Spreadsheets.get(this.ssId, {
      fields: "sheets(properties(sheetId,title,gridProperties))",
    });
    const sheet = sheets.find(
      (s) =>
        s.properties[this.sheetId.toString() ? "sheetId" : "title"] ==
        (this.sheetId || this.sheetName)
    );
    if (!sheet) {
      throw new Error("No sheet.");
    }
    const {
      sheetId,
      gridProperties: { rowCount, columnCount },
    } = sheet.properties;
    this.sheetId = sheetId;
    this.rowCount = rowCount;
    this.columnCount = columnCount;
  }

  /**
   * Convert column lettet to column index.
   * @property {string} letter Column letter.
   * @return {number} Column index.
   */
  columnLetterToIndex_(letter = null) {
    return [...letter.toUpperCase()].reduce(
      (c, e, i, a) =>
        (c += (e.charCodeAt(0) - 64) * Math.pow(26, a.length - i - 1)),
      -1
    );
  }

  /**
   * Request batchUpdate of Sheets API.
   * @property {object[]} requests Request body.
   * @return {object} Returned value from Sheets API.
   */
  sheetsAPI_(requests) {
    return Sheets.Spreadsheets.batchUpdate({ requests }, this.ssId);
  }

  /**
   * Show only the inputted rows and columns.
   * @return
   */
  show() {
    const [hideRows, hideColumns] = [
      [this.rowCount, this.rows],
      [this.columnCount, this.columns],
    ].map(([a, b]) =>
      [...Array(a)]
        .map((_, i) => i)
        .filter(
          (i) =>
            !b.some(({ start, end }) => {
              for (let j = start - 1; j < end; j++) {
                if (i == j) return true;
              }
              return false;
            })
        )
    );
    const requests1 = hideRows.map((e) => ({
      updateDimensionProperties: {
        range: {
          sheetId: this.sheetId,
          startIndex: e,
          endIndex: e + 1,
          dimension: "ROWS",
        },
        properties: { hiddenByUser: true },
        fields: "hiddenByUser",
      },
    }));
    const requests2 = hideColumns.map((e) => ({
      updateDimensionProperties: {
        range: {
          sheetId: this.sheetId,
          startIndex: e,
          endIndex: e + 1,
          dimension: "COLUMNS",
        },
        properties: { hiddenByUser: true },
        fields: "hiddenByUser",
      },
    }));
    const requests = [...requests1, ...requests2];
    this.sheetsAPI_(requests);
    return null;
  }

  /**
   * As reset, show all rows and columns.
   * @return
   */
  reset() {
    const requests = [
      {
        updateDimensionProperties: {
          range: { sheetId: this.sheetId, dimension: "COLUMNS" },
          properties: { hiddenByUser: false },
          fields: "hiddenByUser",
        },
      },
      {
        updateDimensionProperties: {
          range: { sheetId: this.sheetId, dimension: "ROWS" },
          properties: { hiddenByUser: false },
          fields: "hiddenByUser",
        },
      },
    ];
    this.sheetsAPI_(requests);
    return null;
  }
}

Sample script 1

This sample script shows only the inputted rows and columns.

function sample1() {
  // Please set sheet name.
  const sheetName = "Sheet1";

  // Please set showing rows.
  const rows = [
    { start: 2, end: 3 },
    { start: 6, end: 6 },
    { start: 10, end: 15 },
  ];
  // Please set showing columns as column letters.
  const columns = [
    { start: "B", end: "E" },
    { start: "G", end: "G" },
    { start: "I", end: "J" },
  ];

  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const src = new ShowRowsColumns({ rows, columns, spreadsheetId, sheetName }); // Here, Class ShowRowsColumns is used.
  src.show();
}

Sample script 2

This sample script shows only the inputted rows and columns, and the sheet is exported as a PDF file. By this, the sheet showing only the specific rows and columns can be exported as a PDF file.

function sample2() {
  // Please set sheet ID.
  const sheetId = 0;

  // Please set showing rows.
  const rows = [
    { start: 2, end: 3 },
    { start: 6, end: 6 },
    { start: 10, end: 15 },
  ];
  // Please set showing columns as column letters.
  const columns = [
    { start: "B", end: "E" },
    { start: "G", end: "G" },
    { start: "I", end: "J" },
  ];

  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const src = new ShowRowsColumns({ rows, columns, spreadsheetId, sheetId }); // Here, Class ShowRowsColumns is used.
  src.show();

  // Export as PDF file.
  const url = `https://docs.google.com/spreadsheets/export?exportFormat=pdf&id=${spreadsheetId}&gid=${sheetId}`;
  const res = UrlFetchApp.fetch(url, {
    headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
  });
  DriveApp.createFile(res.getBlob());

  src.reset();
}

Testing