GasDataFetchUtils

GasAdapter.ts

import HttpMethod = GoogleAppsScript.URL_Fetch.HttpMethod;
import { URLSearchParams } from "url";

let gasAdapterInstance = null;

type GasEnv = "gas" | "local";
type FetchMethod = "GET" | "POST";

export class GasAdapter {
  static getInstance(): GasAdapter {
    if (!gasAdapterInstance) {
      gasAdapterInstance = new GasAdapter();
    }
    return gasAdapterInstance;
  }

  private env: GasEnv = "gas";
  private cookies = {};
  private nodeFetch: any;
  public userAgent =
    "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.122 Safari/537.36";

  constructor() {
    if (typeof UrlFetchApp !== "undefined") {
      this.setEnv("gas");
    } else {
      this.setEnv("local");
      this.nodeFetch = require("node-fetch");
    }
  }

  setEnv(env: GasEnv) {
    this.env = env;
  }

  public async fetch(
    url,
    method: FetchMethod = "GET",
    params: any = {},
    headers = {}
  ): Promise<string> {
    console.log("fetch", url, method, params);
    if (this.env === "gas") {
      Utilities.sleep(100);
      if (method === "GET") {
        return await this.gasGetFetch(url, params, headers);
      } else if (method === "POST") {
        return await this.gasPostFetch(url, params, headers);
      }
    } else if (this.env === "local") {
      await new Promise((resolve) => setTimeout(resolve, 1000));
      if (method === "GET") {
        return await this.nodeGetFetch(url, params, headers);
      } else if (method === "POST") {
        return await this.nodePostFetch(url, params, headers);
      }
    }
    throw new Error("unknown env " + this.env);
  }

  private async gasGetFetch(
    url: string,
    params: any = {},
    headers = {}
  ): Promise<string> {
    const options = {
      method: "get" as HttpMethod,
      headers: this.getHeaders(headers),
      followRedirects: false,
    };
    const response = UrlFetchApp.fetch(url, options);

    const responseHeaders: any = response.getAllHeaders();
    const cookies = responseHeaders["Set-Cookie"];
    this.parseCookie(cookies);
    const code = response.getResponseCode();
    if (300 <= code && code < 400) {
      return await this.gasGetFetch(responseHeaders.Location);
    }
    const text = response.getContentText();
    return text;
  }

  private async gasPostFetch(
    url: string,
    params: any = {},
    headers = {}
  ): Promise<string> {
    const options = {
      method: "post" as HttpMethod,
      payload: params,
      headers: this.getHeaders(headers),
      followRedirects: false,
    };
    const response = UrlFetchApp.fetch(url, options);

    const responseHeaders: any = response.getAllHeaders();
    const cookies = responseHeaders["Set-Cookie"];
    this.parseCookie(cookies);
    const code = response.getResponseCode();
    if (300 <= code && code < 400) {
      return await this.gasGetFetch(responseHeaders.Location);
    }
    const text = response.getContentText();
    return text;
  }

  private async nodeGetFetch(
    url: string,
    params: any = {},
    headers = {}
  ): Promise<string> {
    const options = {
      headers: this.getHeaders(headers),
      redirect: "manual",
    };
    const res = await this.nodeFetch(url, options);

    const code = res.status;
    const cookies = res.headers.raw()["set-cookie"];
    this.parseCookie(cookies);
    if (300 <= code && code < 400) {
      return await this.nodeGetFetch(res.headers.raw().location);
    }
    const text = await res.text();
    return text;
  }

  private async nodePostFetch(
    url: string,
    params: any = {},
    headers = {}
  ): Promise<string> {
    const schemeParams = new URLSearchParams();
    for (const key of Object.keys(params)) {
      schemeParams.append(key, params[key]);
    }
    const options = {
      method: "POST",
      body: schemeParams,
      headers: this.getHeaders(headers),
      redirect: "manual",
    };
    const res = await this.nodeFetch(url, options);

    const code = res.status;
    const cookies = res.headers.raw()["set-cookie"];
    this.parseCookie(cookies);
    if (300 <= code && code < 400) {
      return await this.nodeGetFetch(res.headers.raw().location);
    }
    const text = await res.text();
    return text;
  }

  private getHeaders(additionalHeaders: { [key: string]: string }): any {
    const header = {
      Cookie: this.cookieString(),
      "user-agent": this.userAgent,
    };
    for (const key of Object.keys(additionalHeaders)) {
      header[key] = additionalHeaders[key];
    }
    return header;
  }

  private cookieString() {
    const result = [];
    for (const key of Object.keys(this.cookies)) {
      result.push(`${key}=${this.cookies[key]}`);
    }
    return result.join(";");
  }

  private parseCookie(cookies: string[] | string) {
    if (!Array.isArray(cookies)) {
      if (!cookies) {
        return;
      }
      cookies = [cookies];
    }
    cookies
      .map((entry) => {
        const parts = entry.split("; ");
        const cookiePart = parts[0];
        const keyValue = cookiePart.split("=");
        this.cookies[keyValue[0]] = keyValue[1];
      })
      .join(";");
  }

  formatDate(date, format) {
    format = format.replace(/yyyy/g, date.getFullYear());
    format = format.replace(/MM/g, ("0" + (date.getMonth() + 1)).slice(-2));
    format = format.replace(/dd/g, ("0" + date.getDate()).slice(-2));
    format = format.replace(/HH/g, ("0" + date.getHours()).slice(-2));
    format = format.replace(/mm/g, ("0" + date.getMinutes()).slice(-2));
    format = format.replace(/ss/g, ("0" + date.getSeconds()).slice(-2));
    format = format.replace(/SSS/g, ("00" + date.getMilliseconds()).slice(-3));
    return format;
  }

  subString(target: string, begin: string, end: string): string[] {
    const result = [];
    let index = 0;
    while (index < target.length) {
      let html = target.substring(index);
      const startIndex = html.indexOf(begin);
      if (startIndex === -1) {
        break;
      }
      html = html.substring(startIndex + begin.length);
      const endIndex = html.indexOf(end);
      if (endIndex === -1) {
        break;
      }
      result.push(html.substring(0, endIndex));
      index += startIndex + endIndex + end.length;
    }
    return result;
  }
}

SpreadsheetAdapter.ts

import Spreadsheet = GoogleAppsScript.Spreadsheet.Spreadsheet;
import Sheet = GoogleAppsScript.Spreadsheet.Sheet;

type RecordCategory = "HealthPlanet" | "MyFitnessPal" | "GarminConnect";

type RecordColumnName = {
  HealthPlanet:
    | "体重 (kg)"
    | "筋肉量 (kg)"
    | "筋肉スコア"
    | "内臓脂肪レベル2"
    | "内臓脂肪レベル"
    | "基礎代謝量 (kcal)"
    | "体内年齢 (才)"
    | "推定骨量 (kg)";
  MyFitnessPal:
    | "合計 カロリー (kcal)"
    | "合計 炭水化物 (g)"
    | "合計 脂肪 (g)"
    | "合計 タンパク質 (g)"
    | "合計 ナトリウム (mg)"
    | "合計 糖分 (g)"
    | "目標 カロリー (kcal)"
    | "目標 炭水化物 (g)"
    | "目標 脂肪 (g)"
    | "目標 タンパク質 (g)"
    | "目標 ナトリウム (mg)"
    | "目標 糖分 (g)"
    | "残り カロリー (kcal)"
    | "残り 炭水化物 (g)"
    | "残り 脂肪 (g)"
    | "残り タンパク質 (g)"
    | "残り ナトリウム (mg)"
    | "残り 糖分 (g)"
    | "追加エクササイズ (kcal)";
  GarminConnect: any;
};

const spreadsheetAdapterInstance = {};

export class SpreadsheetAdapter<T extends RecordCategory> {
  static getInstance(category: RecordCategory): SpreadsheetAdapter<any> {
    if (!spreadsheetAdapterInstance[category]) {
      spreadsheetAdapterInstance[category] = new SpreadsheetAdapter(category);
    }
    return spreadsheetAdapterInstance[category];
  }

  private useCache: boolean = false;
  private cachedSheet: any[][];
  private category: RecordCategory;

  private spreadsheet: Spreadsheet;
  private sheet: Sheet;

  constructor(category: T) {
    this.category = category;
    this.spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    this.sheet = this.spreadsheet.getSheetByName(this.category);
  }

  private getTitleRowNo() {
    if (this.category === "GarminConnect") {
      return 2;
    }
    return 1;
  }
  record(date: Date, type: RecordColumnName[T], value: number) {
    const col = this.findColNoOf(type);
    const row = this.findRowNoOf(date);
    console.log("record", type, col, date, row, value);
    this.setCellValue(row, col, value);
  }

  getValue(date: Date, type: RecordColumnName[T]): any {
    const col = this.findColNoOf(type);
    const row = this.findRowNoOf(date);
    return this.getCellValue(row, col);
  }

  findColNoOf(type: RecordColumnName[T]): number {
    const cols = this.getOneRowValues(this.getTitleRowNo());
    const colNo = cols.indexOf(type);

    if (colNo < 0) {
      throw new Error(
        "unknown column :" + type + " in " + JSON.stringify(cols)
      );
    }
    return colNo + 1;
  }

  findRowNoOf(date: Date): number {
    const dateStirng = this.date2datestring(date);
    const lastRow = this.sheet.getDataRange().getLastRow();
    const rows = this.getOneColValues(1).map((e) => this.date2datestring(e));
    const rowNo = rows.indexOf(dateStirng);
    if (rowNo < 0) {
      if (this.datestring2date(rows[1]).getTime() > date.getTime()) {
        return 2;
      }

      const lastRowDateString = rows[rows.length - 1];
      const lastDate = this.datestring2date(lastRowDateString);
      let count = 1;
      for (
        let i = this.addDay(lastDate, 1);
        i.getTime() <= date.getTime();
        i = this.addDay(i, 1)
      ) {
        this.setCellValue(lastRow + count, 1, i);
        count++;
      }
      return lastRow + count - 1;
    }
    return rowNo + 1;
  }

  date2datestring(date: Date): string {
    // @ts-ignore
    if (!(date instanceof Date)) {
      return String(date);
    }
    return Utilities.formatDate(date, "JST", "yyyy/MM/dd");
  }

  datestring2date(dateString: string): Date {
    const dateArray = dateString.split("/").map((e) => parseInt(e, 10));
    return new Date(dateArray[0], dateArray[1] - 1, dateArray[2]);
  }

  addDay(date: Date, add: number) {
    return new Date(date.getFullYear(), date.getMonth(), date.getDate() + add);
  }

  startCache() {
    this.useCache = true;

    const lastCol = this.sheet.getDataRange().getLastColumn();
    const lastRow = this.sheet.getDataRange().getLastRow();
    this.cachedSheet = this.sheet.getRange(1, 1, lastRow, lastCol).getValues();
  }

  writeCache() {
    if (this.useCache) {
      const lastCol = this.cachedSheet[0].length;
      const lastRow = this.cachedSheet.length;
      this.sheet.getRange(1, 1, lastRow, lastCol).setValues(this.cachedSheet);
    }
  }

  endCache() {
    this.writeCache();
    this.useCache = false;
  }

  private getCellValue(row: number, col: number): any {
    if (!this.useCache) {
      return this.sheet.getRange(row, col, 1, 1).getValue();
    }
    return this.cachedSheet[row - 1][col - 1];
  }

  private setCellValue(row: number, col: number, value: any) {
    if (!this.useCache) {
      return this.sheet.getRange(row, col, 1, 1).setValue(value);
    }
    for (let i = this.cachedSheet[0].length; i < col; i++) {
      this.cachedSheet.forEach((e) => e.push(""));
    }
    const maxCol = this.cachedSheet[0].length;
    for (let i = this.cachedSheet.length; i < row; i++) {
      this.cachedSheet.push(new Array(maxCol).map((e) => ""));
    }
    this.cachedSheet[row - 1][col - 1] = value;
  }

  private getOneColValues(no) {
    if (!this.useCache) {
      const lastRow = this.sheet.getDataRange().getLastRow();
      const raw = this.sheet.getRange(1, no, lastRow, 1).getValues();
      return raw.map((e) => e[0]);
    }
    return this.cachedSheet.map((e) => e[no - 1]);
  }

  private getOneRowValues(no) {
    if (!this.useCache) {
      const lastCol = this.sheet.getDataRange().getLastColumn();
      const values = this.sheet.getRange(no, 1, 1, lastCol).getValues();
      return values[0];
    }
    return this.cachedSheet[no - 1];
  }

  private getLastColumn() {
    if (!this.useCache) {
      return this.sheet.getDataRange().getLastColumn();
    }
    return this.cachedSheet[0].length;
  }
}