Google スプレッドシート #Sheet: TSVで上書き

// Script Example of Business Process Automation
// for 'engine type: 3' ("GraalJS standard mode")
// cf. 'engine type: 2' ("GraalJS Nashorn compatible mode") (renamed from "GraalJS" at 20230526)


//////// START "main()" /////////////////////////////////////////////////////////////////
main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const qAuthzSetting   = configs.getObject ("AuthzConfU"); // REQUIRED
  if (qAuthzSetting === null) {
    throw new Error("\n AutomatedTask ConfigError: Config {AuthzConfU} invalid (OAUTH2) \n");
  }
  engine.log(" AutomatedTask Config: Authz Setting: " + qAuthzSetting.getName());

const strTsv          = configs.get       ( "StrConfA1" );      // REQUIRED
  if( strTsv === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A1: TSV} is empty \n" );
  }
  const arr2dTsv      = parseAsRectangular ( strTsv );          // as arr2dTsv[Y][X]
  if (arr2dTsv.length === 0) {
    throw new Error( "\n AutomatedTask DataError: TSV has no non-blank rows \n" );
  }
const strColIds       = configs.get       ( "StrConfA2" );      // REQUIRED
  if( strColIds === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A2: Column IDs} not specified \n" );
  }
  let arrColIds       = strColIds.split(",");
  engine.log( " AutomatedTask Config: Column Ids (" +
                strColIds + ") [" + 
                arrColIds.length + "] will be inserted" );
const strDocId        = configs.get       ( "StrConfB1" );      // REQUIRED
  if( strDocId === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {B1: Spreadsheet ID} not specified \n" );
  }
const strSheetId      = configs.get       ( "StrConfB2" );      // not required
  let numSheetId      = strSheetId !== "" ?
                        parseInt( strSheetId, 10 ) :
                        0;
const strStartX       = configs.get       ( "StrConfB3X" );     // not required
  let numStartX       = strStartX !== "" ?
                        parseInt( strStartX, 10 ) :
                        0;
const strStartY       = configs.get       ( "StrConfB3Y" );     // not required
  let numStartY       = strStartY !== "" ?
                        parseInt( strStartY, 10 ) :
                        0;
const strSheetName    = configs.get       ( "StrConfB2b");      // not required

const numPocketInY    = configs.getObject ( "SelectConfInY" );  // not required
const numPocketInX    = configs.getObject ( "SelectConfInX" );  // not required



////// == Data Retrieving / ワークフローデータの参照 ==
// (Nothing. Retrieved via Expression Language in Config Retrieving)




////// == Calculating / 演算 ==

//// ▼▼▼ get SheetId by SheetName ▼▼▼  ※ if(strSheetName !== "")

if( strSheetName !== "" ){
  engine.log( " AutomatedTask: Try to get SheetId by SheetName. (connecting ...)" );

  /// request0, prepare
  // Google Workspace > Google Sheets > spreadsheets > Method: spreadsheets.get
  // https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/get

  let request0uri = "https://sheets.googleapis.com/v4/spreadsheets/" + strDocId;
  let request0    = httpClient.begin(); // HttpRequestWrapper
      request0    = request0.authSetting( qAuthzSetting ); // with "Authorization: Bearer XX"
      // https://questetra.zendesk.com/hc/en-us/articles/360024574471-R2300#HttpRequestWrapper

  /// request0, try
  const response0 = request0.get( request0uri );             // HttpResponseWrapper
  const response0code = response0.getStatusCode() + "";      // (primitive string)
  const response0body = response0.getResponseAsString() + "";
  engine.log( " AutomatedTask ApiResponse Status: " + response0code );
  if( response0code !== "200"){
    throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                      response0code + "\n" + response0body + "\n" );
  }

  /// response0, parse
  /* engine.log( response0body ); // debug
  {
    "spreadsheetId": "17rwXXXXxS34yyyyYr-KzzzzX0_iWWWWOfHdxxxxRCLM",
    "properties": { … },
    "sheets": [
      {
        "properties": {
          "sheetId": 0,
          "title": "sheet1",
          "index": 0,
          "sheetType": "GRID",
          "gridProperties": {
            "rowCount": 100,
            "columnCount": 20,
            "frozenRowCount": 1
          }
        }
      }
    ],
    "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/17rw......OfHdxxxxRCLM/edit"
  }
  */
  const response0obj = JSON.parse( response0body );

  let boolFound = false;
  for( let i = 0; i < response0obj.sheets.length; i++ ){
    engine.log( " - " + response0obj.sheets[i].properties.title );
    if( response0obj.sheets[i].properties.title === strSheetName ){
      numSheetId = response0obj.sheets[i].properties.sheetId;
      engine.log( " AutomatedTask SheetName '" + strSheetName +
                    "': SheetId " + numSheetId );
      boolFound = true;
      break;
    } // not found, then default SheetID "0"
  }
  if (! boolFound ) {
    engine.log(" AutomatedTask RuntimeWarning: " + strSheetName + " not found. Using sheetId=0.");
  }

}



//// ▼▼▼ Update Cells ▼▼▼
const regBpmsYMD = /^\d{4}-\d{2}-\d{2}$/;

//// request1, prepare body
// Google Workspace > Google Sheets > spreadsheets > Method: spreadsheets.batchUpdate ( Requests: updateCells )
// https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/request#UpdateCellsRequest

let request1obj = {};
    request1obj.requests = [];
    request1obj.requests[0] = {};
    request1obj.requests[0].updateCells = {};
    request1obj.requests[0].updateCells.fields = "userEnteredValue,userEnteredFormat.numberFormat";
//    request1obj.requests[0].updateCells.fields = "*";
    request1obj.requests[0].updateCells.start = {};
    request1obj.requests[0].updateCells.start.sheetId = numSheetId;
    request1obj.requests[0].updateCells.start.rowIndex = numStartY;
    request1obj.requests[0].updateCells.start.columnIndex = numStartX;
    request1obj.requests[0].updateCells.rows = [];

for (let i = 0; i < arr2dTsv.length; i++) {  // arr2dTsv[Y][X]
  request1obj.requests[0].updateCells.rows[i] = { values: [] };

  for (let j = 0; j < arrColIds.length; j++) {
    request1obj.requests[0].updateCells.rows[i].values[j] = {};
    const v = request1obj.requests[0].updateCells.rows[i].values[j];

    const cid = arrColIds[j].trim();
    const numTmpColId = parseInt(cid, 10);
    const raw = (arr2dTsv[i][numTmpColId] ?? "");
    const rawStr = String(raw);

    if (cid.slice(-1) === "n") { // Numeric
      v.userEnteredValue = {};
      const n = parseFloat(rawStr.replace(/,/g, ""));
      if (Number.isNaN(n)) {
        v.userEnteredValue.stringValue = "";
      } else {
        v.userEnteredValue.numberValue = n;
      }

    } else if (cid.slice(-1) === "d") { // Date
      v.userEnteredValue  = {};
      v.userEnteredFormat = { numberFormat: {} };

      let strDatetimeTmp = rawStr.replace(/\//g, "-");
      if (regBpmsYMD.test(strDatetimeTmp)) { strDatetimeTmp += "T00:00:00"; }
      const dateTmp = new Date(strDatetimeTmp);

      if (isNaN(dateTmp.getTime())) {
        v.userEnteredValue.stringValue = "";
      } else {
        let numSerial = dateTmp.getTime() / 86400000 + 25569;
        numSerial += engine.getTimeZoneOffsetInMinutes() / (60 * 24); // 現行ロジック踏襲
        v.userEnteredFormat.numberFormat.type = "DATE";
        v.userEnteredValue.numberValue = numSerial;
      }

    } else { // String
      v.userEnteredValue = { stringValue: rawStr };
    }
  }
}


//// request1, prepare header
// Google Workspace > Google Sheets > spreadsheets > Method: spreadsheets.batchUpdate
// https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/batchUpdate

let request1uri = "https://sheets.googleapis.com/v4/spreadsheets/" + strDocId + ":batchUpdate";

let request1    = httpClient.begin(); // HttpRequestWrapper
    request1    = request1.authSetting( qAuthzSetting ); // with "Authorization: Bearer XX"
    request1    = request1.body( JSON.stringify( request1obj ), "application/json" );


//// request1, try
const response1     = request1.post( request1uri );        // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest1 Start: " + request1uri );
const response1code = response1.getStatusCode() + "";      // (primitive string)
const response1body = response1.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + response1code );
if( response1code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response1code + "\n" + response1body + "\n" );
}

//// response1, parse
// (nothing)
/* engine.log( response1body ); // debug
{
  "spreadsheetId": "1rviXXXXXHcRYYYYjFN1ZZZZfM6EWWWWqifaVVVVzgSM",
  "replies": [
    {}
  ]
}


*/




//// == Data Updating / ワークフローデータへの代入 ==
if ( numPocketInY !== null ){ 
  engine.setData( numPocketInY, new java.math.BigDecimal( arr2dTsv.length ) );
}
if ( numPocketInX !== null ){ 
  engine.setData( numPocketInX, new java.math.BigDecimal( arr2dTsv[0].length ) );
}


} //////// END "main()" /////////////////////////////////////////////////////////////////




/**
 * Parses a TSV (Tab-Separated Values) string and returns a 2D array with rectangular shape.
 * This function ensures that each row in the resulting 2D array has the same number of columns,
 * equal to the widest row in the original TSV input. Shorter rows are padded with empty strings.
 * Blank lines in the input are ignored.
 * Additionally, the function logs the minimum and maximum column counts (excluding blank lines),
 * along with the total number of lines and count of blank lines.
 * 
 * TSV（タブ区切り値）形式の文字列を解析し、2次元の配列として整形して返します。
 * 入力されたTSV文字列を行単位で分割し、各行のセル数をそろえて「長方形（矩形）」の形に整えます。
 * 最もセル数が多い行に合わせて、セル数が足りない行には空文字列（""）を補完します。
 * 空行（中身が空の行）は無視されます。
 * また、最小列数・最大列数・全体の行数・空行の数をログに出力します（engine.log）。
 * 
 * 
 * @param {string} strTsv - The raw TSV string, with rows separated by newline characters (`\n`) 
 *                          and cells separated by tab characters (`\t`).
 * 
 * @returns {string[][]} A two-dimensional array where each sub-array represents a row from the TSV input,
 *                       padded with empty strings to ensure uniform column width.
 * 
 * @example
 * const tsv = "a\tb\tc\n1\t2\nx\ty\tz\tw\n";
 * const result = parseAsRectangular(tsv);
 * // result:
 * // [
 * //   ["a", "b", "c", ""],
 * //   ["1", "2", "", ""],
 * //   ["x", "y", "z", "w"]
 * // ]
 */

function parseAsRectangular( strTsv ){
  const arrTsv = strTsv.split("\n");

  /// Get numMinWidth and numMaxWidth (blank lines are excluded)
  let numMinWidth   = Infinity; // cf. String-Type Max: 1 million
  let numMaxWidth   = 0;
  let numBlanklines = 0;
  for( let i = 0; i < arrTsv.length; i++ ){
    if( arrTsv[i] === "" ){ // Skip blank lines
      numBlanklines += 1;
      continue;
    }
    let arrCells = arrTsv[i].split("\t");
    if( numMinWidth > arrCells.length ){ numMinWidth = arrCells.length; }
    if( numMaxWidth < arrCells.length ){ numMaxWidth = arrCells.length; }
  }
  engine.log( " AutomatedTask TsvDataCheck:" + 
              " MinWidth:" + numMinWidth +
              " MaxWidth:" + numMaxWidth +
              " Lines:" + arrTsv.length +
              " (BlankLines:" + numBlanklines + ")" );

  /// Get numMinWidth and numMaxWidth (blank lines are excluded)
  let arr2dTsv      = [];
  for( let i = 0; i < arrTsv.length; i++ ){
    if( arrTsv[i] === "" ){ // Skip blank lines
      continue;
    }
    let arrTmp = [];
    let arrCells = arrTsv[i].split("\t");
    for( let j = 0; j < numMaxWidth; j++ ){
      if( j < arrCells.length ){
        arrTmp[j] = arrCells[j];
      }else{
        arrTmp[j] = "";
      }
    }
    arr2dTsv.push( arrTmp );
  }

  return arr2dTsv;
}




/*
▼NOTES:
- By placing this [Automated Step] in a workflow diagram, an API request will be generated each time a Case Process reaches it.
    - "Questetra BPM Suite" server (client side) ⇒ "Google Spreadsheets" API (server side)
    - Tab Separated Values text (TSV string) data will overwrite all data in the sheet.
- See the URL for the "Document ID" (Spreadsheet ID) and "Sheet ID" (Sheet ID).
    - `https://docs.google.com/spreadsheets/d/{SPREADSHEETID}/edit#gid={SHEETID}`
    - If a "Sheet Name" (SheetName) is set (Expert mode), the SheetName takes priority and the SheetID setting is ignored.
- Workflow apps that include this [Automated Step] require [HTTP Authentication Settings].
    - Create [Authentication Information] (OAuth client) in the "Google Cloud Console."
        - https://console.cloud.google.com/apis/credentials
        - Redirect URI: `https://s.questetra.net/oauth2callback`
        - ⇒ Obtain a [Client ID] and [Client Secret]
    - Configure the access token acquisition method in the [HTTP Authentication Settings] of this WorkflowApp.
        - Set the [Authorization Endpoint URL]:
            - `https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force`
        - Set the [Token Endpoint URL]:
            - `https://accounts.google.com/o/oauth2/token`
        - Example [Scope] setting:
            - `https://www.googleapis.com/auth/spreadsheets`
        - (Other [Scope] setting examples):
            - `https://www.googleapis.com/auth/drive`
            - `https://www.googleapis.com/auth/drive.file`
            - `https://www.googleapis.com/auth/spreadsheets`
            - `https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/presentations`
        - ⇒ Click [Get Token]. (You will grant the API requests.)
            - The refresh token will be stored.
            - Refresh token → Access token → API communication

▼NOTES-ja:
- この［自動工程］をワークフロー図に配置すれば、案件プロセスが到達するたびに「APIリクエスト」が発生します
    - "Questetra BPM Suite" サーバ（クライアント側）　⇒　"Google スプレッドシート" の API（サーバ側）
    - Tab Separated Values テキスト（TSV文字列）のデータで、シート内のデータが一括上書きされます
- "ドキュメントID"（Spreadsheet ID）および "シートID"（Sheet ID）は、シートを表示した際の URL を参照してください
    - `https://docs.google.com/spreadsheets/d/{SPREADSHEETID}/edit#gid={SHEETID}`
    - "シート名" (SheetName) が設定された場合、SheetName 設定が優先されます（SheetID 設定は無視されます）
- この［自動工程］を含むワークフローアプリには、［HTTP 認証設定］が必要です
    - "Google Cloud コンソール" にて、［認証情報］（OAuthクライアント）を作成します
        - https://console.cloud.google.com/apis/credentials
        - リダイレクトURI: `https://s.questetra.net/oauth2callback`
        - ⇒［クライアントID］（ClientId）と［クライアントシークレット］ (Client Secret) を取得
    - ワークフローアプリ等の［HTTP 認証設定］にて、アクセストークン取得方法を設定します
        - ［認可エンドポイントURL］(Authorization Endpoint URL) の設定:
            - `https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force`
        - ［トークンエンドポイントURL］(Token Endpoint URL) の設定:
            - `https://accounts.google.com/o/oauth2/token`
        - ［スコープ］設定例:
            - `https://www.googleapis.com/auth/spreadsheets`
        - （他の［スコープ］設定例）:
            - `https://www.googleapis.com/auth/drive`
            - `https://www.googleapis.com/auth/drive.file`
            - `https://www.googleapis.com/auth/spreadsheets`
            - `https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/presentations`
        - ⇒［トークンの取得］(Get Token) をクリックします。（「APIリクエスト」を許諾します）
            - リフレッシュトークン (Refresh Token) が格納されます。
            - リフレッシュトークン (Refresh Token) → アクセストークン (Access Token)  → API通信

▼APPENDIX:
- Numeric strings are evaluated using the `parseFloat()` function after removing commas (thousand separators).
    - Cells that fail evaluation are left blank.
- Datetime strings are evaluated using the `Date()` function.
    - Cells that fail evaluation are left blank.
    - `new Date('December 17, 1995 03:24:00')`
    - `new Date('1995-12-17T03:24:00')`
    - `new Date('1995-12-17 03:24')`
    - `new Date('1995-12-17')`
    - Note that "T00:00:00" is automatically added (to convert to local time) only for the "YYYY-MM-DD" and "YYYY/MM/DD" formats.
- TSV (Tab Separated Time) Values) text assumes rectangular data.
    - "Monthly Sales Summary," "Monthly Access Summary," "PIVOT Table," etc.
    - Non-rectangular data is automatically formatted with an empty string (adjusted to the longest number of columns).
    - Blank lines (including trailing newlines) are ignored.
    - CF: "Questetra BPMS: Process, Batch Extract by Filter as TSV"
        - https://support.questetra.com/addons/questetra-bpms-process-batch-extract-by-filter-as-tsv-2021/
- Charts in Google Sheets update automatically.
    - Charts referenced in Google Slides do not update automatically.
    - CF: "Google Slides: Page, Refresh Charts"
        - https://support.questetra.com/ja/addons/google-slides-page-refresh-charts-2021/
    - CF: "Google Slides: Page, Export as PNG"
        - https://support.questetra.com/ja/addons/google-slides-page-export-as-png-2021/
- Overridden via the Google Sheets API v4 "UpdateCellsRequest"
    - Unlike the "DeleteRangeRequest", the chart's data range is not deleted.

▼APPENDIX-ja:
- 数値文字列は、カンマ（桁区切り文字）が除去された上で、`parseFloat()` 関数で評価されます
    - 評価に失敗したセルは、何も追記されません
- 日時文字列は、`Date()` 関数で評価されます
    - 評価に失敗したセルは、何も追記されません
    - `new Date('December 17, 1995 03:24:00')`
    - `new Date('1995-12-17T03:24:00')`
    - `new Date('1995-12-17 03:24')`
    - `new Date('1995-12-17')`
    - なお "YYYY-MM-DD" 書式および "YYYY/MM/DD" 書式の場合に限り "T00:00:00" が自動付加（ローカルタイム化）されます
- TSV（Tab Separated Values）テキストは、矩形データを前提とします
    - "月次売上集計"、"月次アクセス集計"、"PIVOTテーブル"、など
    - 矩形でないデータは、空文字列によって自動整形されます（最も長い列数に合わせる）
    - 空行（末尾改行を含む）は無視されます
    - CF: "Questetra BPMS #案件: 保存済フィルタでTSV一括抽出"
        - https://support.questetra.com/ja/addons/questetra-bpms-process-batch-extract-by-filter-as-tsv-2021/
- Google スプレッドシート内のグラフ（Charts）は自動的に更新されます
    - Google スライドで参照されているグラフ（Charts）は自動的には更新されません
    - CF: "Google スライド #ページ: グラフ全更新"
        - https://support.questetra.com/ja/addons/google-slides-page-refresh-charts-2021/
    - CF: "Google スライド #ページ: PNGエクスポート"
        - https://support.questetra.com/ja/addons/google-slides-page-export-as-png-2021/
- Google Sheets API v4 の "UpdateCellsRequest" を通じて書き換えられます
    - "DeleteRangeRequest" とは異なりグラフ（Charts）の［データ範囲］が削除されることはありません

*/