[Google spreadsheet tools] #gas #tools #helpers #spreadsheet #sheet #table

config.ts

import once from 'lodash/once'
import { cleanEnv, str } from 'envalid'
import { getSheetAsRecord } from './tools'

export function getDocumentProperties() {
  return PropertiesService.getDocumentProperties().getProperties()
}

export function setDocumentProperties(properties: { [key: string]: string }) {
  const props = PropertiesService.getDocumentProperties().setProperties(
    properties,
    true
  )

  return props.getProperties()
}

const ConfigNameMap: Record<string, string> = {
  'Дата начала календаря': 'CALENDAR_FIRST_DATE',
  'Наименование листа с календарем': 'CALENDAR_SHEET_NAME'
}

export const getConfigRecord = once(() => {
  const config = getSheetAsRecord('Настройки')

  const record = Object.keys(ConfigNameMap).reduce((res, key) => {
    if (Object.prototype.hasOwnProperty.call(config, key)) {
      res[ConfigNameMap[key]] = config[key]
    } else {
      throw new Error(`На листе "Настройки" не указан параметр - ${key}`)
    }
    return res
  }, {} as Record<string, unknown>)

  return record
})

/**
 * Возвращает объект с параметрами документа, установленными через форму.
 *
 * @returns Параметры документа
 */
function getEnvRecord() {
  const properties = getDocumentProperties()

  const record = Object.keys(properties).reduce((res, key) => {
    const val = properties[key]

    try {
      const parsed = JSON.parse(val) as { value: any }

      res[key] = Object.prototype.hasOwnProperty.call(parsed, 'value')
        ? parsed.value
        : val
    } catch (err) {
      res[key] = val // Вероятно значение св-ва не обернуто в JSON
    }

    return res
  }, {} as Record<string, string>)

  return record
}

export const getEnv = once(() => {
  const env = cleanEnv(
    {
      ...getEnvRecord(),
      ...getConfigRecord()
    },
    {
      FOO: str()
    }
  )

  return env
})

getRangeValuesFromRecords.ts

export function getRangeValuesFromRecords(records: Record<string, any>[]) {
  if (records.length === 0) return [];

  const headers = Object.keys(records[0]);

  const rangeValues = records.map((record) =>
    headers.map((header) => {
      const value = record[header];
      return value == null ? "" : value;
    })
  );

  return [headers, ...rangeValues];
}

getSheetAsRecord.ts

/**
 * Преобразует строки листа в объект где ключами являются начения первой колонки
 * а значениями данные из второй колонки.
 *
 * @version 1.3
 *
 * @param sheet Лист или наименование листа
 * @returns Объект
 */
export function getSheetAsRecord<T = unknown>(
  sheet: string | GoogleAppsScript.Spreadsheet.Sheet
) {
  const sheet_ =
    typeof sheet === "string"
      ? SpreadsheetApp.getActive().getSheetByName(sheet)
      : sheet;

  if (!sheet_) {
    throw new Error(`Не найден лист "${sheet}"`);
  }

  const frozenRows = sheet_.getFrozenRows();

  const values = sheet_.getDataRange().getValues();

  const record = {} as Record<string, T>;

  for (let i = frozenRows; i < values.length; i++) {
    const row = values[i];

    if (typeof row[0] === "string") {
      record[row[0]] = row[1];
    }
  }

  return record;
}

getSheetAsRecords.ts

export function getRecordsFromRangeValues(values: unknown[][]) {
  const header = values[0];

  if (!header) return [];

  const objs = [];

  for (let i = 1; i < values.length; i++) {
    const row = values[i];

    const obj: Record<string, unknown> = {};

    for (const [index, fieldName] of header.entries()) {
      if (fieldName === "" || typeof fieldName !== "string") continue;
      obj[fieldName] = row[index];
    }

    objs.push(obj);
  }

  return objs;
}

/**
 * Возвращает массив объектов на основе данных листа
 *
 * @param sheet Лист или наименование листа
 * @returns Массив записей
 */
export function getSheetAsRecords(
  sheet: string | GoogleAppsScript.Spreadsheet.Sheet
) {
  const _sheet =
    typeof sheet === "string"
      ? SpreadsheetApp.getActive().getSheetByName(sheet)
      : sheet;

  if (!_sheet) {
    throw new Error(`Не найден лист "${sheet}"`);
  }

  const values = _sheet.getDataRange().getValues();

  const rows = getRecordsFromRangeValues(values);

  return rows;
}

getSheetHeaders.js

/**
 * Returns Sheet headers
 *
 * @param {SpreadsheetApp.Sheet} sheet
 */
function getSheetHeaders(sheet) {
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();

  return headers[0];
}

getSheetSelectedRecords.js

/**
 * Returns Sheet selected rows as records
 *
 * @param {SpreadsheetApp.Sheet} sheet
 */
function getSheetSelectedRecords(sheet) {
  if (!sheet) {
    throw new Error("Не указан обязательный аргумент sheet");
  }

  const headers = getSheetHeaders(sheet);

  const headerIndexesEntries = headers.flatMap((h, index) => {
    return h == null || typeof h !== "string" || h === "" ? [] : [[h, index]];
  });

  const headerIndexesMap = new Map(headerIndexesEntries);

  const activeRange = sheet.getSelection().getActiveRange();

  if (!activeRange) {
    throw new Error("Необходимо выбрать строки, наложив на них выделение.");
  }

  const firstRowIndex = activeRange.getRow();
  const lastRowIndex = activeRange.getLastRow();

  const rows = sheet
    .getRange(
      firstRowIndex,
      1,
      lastRowIndex - firstRowIndex + 1,
      sheet.getLastColumn()
    )
    .getValues();

  if (rows.length === 0) {
    throw new Error("Пустое выделение");
  }

  if (rows[0].length !== headers.length) {
    throw new Error(
      "Кол-во колонок в выделении не соответствует кол-ву заголовков"
    );
  }

  const records = rows.map((r) => {
    const recordEntries = [];

    for (const [h, index] of headerIndexesMap.entries()) {
      recordEntries.push([h, r[index]]);
    }

    const record = Object.fromEntries(recordEntries);

    return record;
  });

  return records;
}