Google スプレッドシート: 範囲データ, TSVエクスポート

// GraalJS Script (engine type: 2)

//////// START "main()" /////////////////////////////////////////////////////////////////

main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const strAuthzSetting     = configs.get      ( "AuthzConfU" );   /// REQUIRED
  engine.log( " AutomatedTask Config: Authz Setting: " + strAuthzSetting );
const strInputfileId      = configs.get      ( "StrConfA1" );    /// REQUIRED
  if( strInputfileId    === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A1: FileID} is empty \n" );
  }
const strValueRange1      = configs.get      ( "StrConfB1" );    // NotRequired
const strValueRange2      = configs.get      ( "StrConfB2" );    // NotRequired
  if( strValueRange1 === "" && strValueRange2 === ""){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {ValueRange B1} and {ValueRange B2} are both empty \n" );
  }
const strPocketTsv1       = configs.getObject( "SelectConfC1" ); // NotRequired
const strPocketTsv2       = configs.getObject( "SelectConfC2" ); // NotRequired


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


//// == Calculating / 演算 ==
/// Replace All Text via Requests
/// Sheets for Developers > API v4
/// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet
// request1, prepare
let request1Uri = "https://sheets.googleapis.com/v4/spreadsheets/" + strInputfileId + "/values:batchGet";
let request1    = httpClient.begin(); // HttpRequestWrapper
    request1    = request1.authSetting( strAuthzSetting ); // with "Authorization: Bearer XX"
    // https://questetra.zendesk.com/hc/en-us/articles/360024574471-R2300#HttpRequestWrapper
    request1    = request1.queryParam( "majorDimension", "ROWS" );
    request1    = request1.queryParam( "valueRenderOption", "FORMATTED_VALUE" ); 
                 // Even if formatted as currency, return "$1.23" not "1.23".
    request1    = request1.queryParam( "dateTimeRenderOption", "FORMATTED_STRING" ); 
                 // Date as strings (the spreadsheet locale) not SERIAL_NUMBER
    if( strValueRange1 !== "" ){
      request1  = request1.queryParam( "ranges", strValueRange1 );
    }
    if( strValueRange2 !== "" ){
      request1  = request1.queryParam( "ranges", strValueRange2 );
    }
// request1, try
const response1     = request1.get( request1Uri ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest1 Start: " + request1Uri );
const response1Code = response1.getStatusCode() + "";
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
/* 
engine.log( response1Body ); // debug
{
  "spreadsheetId": "1dSQcZSh2kF_KnuzgFydaOx7T24oURRJwpkCvB_qCV4w",
  "valueRanges": [
    {
      "range": "Sheet1!A1:Z3",
      "majorDimension": "ROWS",
      "values": [
        [
          "Date",
          "Account (科目)",
          "Description (摘要)",
          "Debit (借方)",
          "Credit (貸方)",
          "Balance (残高)"
        ],
        [
          "2019-10-01",
          "\u003cBUDGET\u003e",
          "",
          "",
          "4,000,000.00",
          "4,000,000.00"
        ],
        [
          "2019-10-17",
          "Design Fee",
          "Web Display: BPM ",
          "2,000,000.00",
          "",
          "2,000,000.00"
        ]
      ]
    },
    {
      "range": "Sheet1!A6:Z8",
      "majorDimension": "ROWS",
      "values": [
        [
          "2019-11-10",
          "\u003cBUDGET2\u003e",
          "",
          "",
          "500,000.00",
          "1,000,000.00"
        ],
        [
          "2019-11-10",
          "Print",
          "Poster",
          "100,000.00",
          "",
          "900,000.00"
        ],
        [
          "2019-11-25",
          "Print",
          "Pamphlet",
          "400,000.00",
          "",
          "500,000.00"
        ]
      ]
    }
  ]
}
*/
const response1Obj = JSON.parse( response1Body );
engine.log( " AutomatedTask ApiResponse: #of valueRanges: " + response1Obj.valueRanges.length );
let arrStrTsvs = [];
for( let i = 0; i < response1Obj.valueRanges.length; i++ ){
  engine.log( " AutomatedTask ApiResponse: range: " + response1Obj.valueRanges[i].range );
  let numTsvWidth = 0;
  for( let j = 0; j < response1Obj.valueRanges[i].values.length; j++ ){
    if( numTsvWidth < response1Obj.valueRanges[i].values[j].length ){
      numTsvWidth   = response1Obj.valueRanges[i].values[j].length;
    }
  }
  let strTmp = "";
  engine.log( " AutomatedTask ApiResponse: tmpTsv" + (i + 1) + " width: " + numTsvWidth );
  for( let j = 0; j < response1Obj.valueRanges[i].values.length; j++ ){
    for( let k = 0; k < numTsvWidth; k++ ){
      if( k < response1Obj.valueRanges[i].values[j].length ){
        strTmp += response1Obj.valueRanges[i].values[j][k];
      }
      if( k != response1Obj.valueRanges[i].values[j].length - 1 ){
        strTmp += "\t";
      }
    }
    if( j != response1Obj.valueRanges[i].values.length - 1 ){
      strTmp += "\n";
    }
  }
  arrStrTsvs.push( strTmp );
}
// each TSV, which range
let strTsv1 = "";
let strTsv2 = "";
if(       strValueRange1 !== "" && strValueRange2 !== ""){
  strTsv1 = arrStrTsvs[0];
  strTsv2 = arrStrTsvs[1];
}else if( strValueRange1 !== "" && strValueRange2 === ""){
  strTsv1 = arrStrTsvs[0];
}else if( strValueRange1 === "" && strValueRange2 !== ""){
  strTsv2 = arrStrTsvs[0];
}else{
  throw new Error( "\n AutomatedTask UnexpectedSumError: " +
                   "\n" + response1Body + "\n" );
}


//// == Data Updating / ワークフローデータへの代入 ==
if( strPocketTsv1    !== null ){
  engine.setData( strPocketTsv1,    strTsv1 );
}
if( strPocketTsv2    !== null ){
  engine.setData( strPocketTsv2,    strTsv2 );
}


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


/*
Notes:
- The rectangular area in the spreadsheet can be automatically included as Workflow data.
- The File ID can be obtained from the URI or the sharing settings screen.
    - docs.google.com/spreadsheets/d/{File-ID}/edit#gid=0
- How to specify the range depends on Google API A1 notation. (see APPENDIX)
- The exported TSV string has a uniform number of columns (number of cells) in each row.
- There is no line feed code at the end of the exported TSV string.
    - When combining multiple TSVs, it is necessary to insert a line feed code.
Notes-ja:
- スプレッドシート内の矩形範囲がWorkflowデータとして自動的に取り込まれるようになります。
- ドキュメント ID は URL を参照するなどして設定します。
    - docs.google.com/spreadsheets/d/{File-ID}/edit#gid=0
- 範囲の指定方法は Google API の A1 notation に依ります (Appendix参照)
- 出力されるTSV文字列は、いずれの行も均一の列数（セル数）となります。
- 出力されるTSV文字列の末尾に改行コードはありません。
    - 複数のTSVを結合する場合には、改行コードを途中挿入する必要があります。

APPENDIX-en
- A1 Notation
    - https://developers.google.com/sheets/api/guides/concepts#a1_notation
    - "Sheet1!A1:B2" refers to the first two cells in the top two rows of Sheet1.
    - "Sheet1!A:A" refers to all the cells in the first column of Sheet1.
    - "Sheet1!1:2" refers to the all the cells in the first two rows of Sheet1.
    - "Sheet1!A5:A" refers to all the cells of the first column of Sheet 1, from row 5 onward.
    - "A1:B2" refers to the first two cells in the top two rows of the first visible sheet.
    - "Sheet1" refers to all the cells in Sheet1.
- Setting example of "HTTP Authentication" (OAuth2)
    - Authorization Endpoint URL:
        - https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
    - Token Endpoint URL:
        - https://accounts.google.com/o/oauth2/token
    - Scope:
        - https://www.googleapis.com/auth/spreadsheets.readonly
    - Client ID, Consumer Secret:
        - ( from https://console.developers.google.com/ )
        - Redirect URLs: https://s.questetra.net/oauth2callback
APPENDIX-ja
- A1記法（A1 Notation）
    - https://developers.google.com/sheets/api/guides/concepts#a1_notation
    - "Sheet1!A1:B2" 「Sheet1」の上2行の先頭2セル（合計4セル）を参照します。
    - "Sheet1!A:A" 「Sheet1」のA列の全てのセルを参照します。
    - "Sheet1!1:2" 「Sheet1」の上2行にあるすべてのセルを参照します。
    - "Sheet1!A5:A" 「Sheet1」のA列5行目以降のすべてのセルを参照します。
    - "A1:B2" 最初に表示されるシートの上2行の先頭2セル（合計4セル）を参照します。
    - "Sheet1" 「Sheet1」のすべてのセルを参照します。
- "HTTP認証"（OAuth2）の設定例
    - Authorization Endpoint URL:
        - https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
    - Token Endpoint URL:
        - https://accounts.google.com/o/oauth2/token
    - Scope:
        - https://www.googleapis.com/auth/spreadsheets.readonly
    - Client ID, Consumer Secret:
        - ( from https://console.developers.google.com/ )
        - Redirect URLs: https://s.questetra.net/oauth2callback
*/