Google スプレッドシート: 行範囲, 削除

// 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 strFileId           = configs.get      ( "StrConfA1" );    /// REQUIRED
  if( strFileId         === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A1: FileID} is empty \n" );
  }
let   strSheetId          = configs.get      ( "StrConfA2" );    // NotRequired
let   strSheetName        = configs.get      ( "StrConfA3" );    // NotRequired
  if( strSheetId === "" ){
    engine.log( " AutomatedTask ConfigWarning:" +
                " Config {A2: SheetId} (recommended) is empty" );
    if( strSheetName === "" ){
      throw new Error( "\n AutomatedTask ConfigError:" +
                       " Config {A2: Id} or {A3: Name} must be specified \n" );
    }
  }
const strRowRangeStart    = configs.get      ( "StrConfB1" );    /// REQUIRED
  let numRowRangeStart    = parseInt( strRowRangeStart, 10 );
const strRowRangeEnd      = configs.get      ( "StrConfB2" );    /// REQUIRED
  let numRowRangeEnd      = parseInt( strRowRangeEnd, 10 );
  if( numRowRangeStart >= numRowRangeEnd ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " {B1: StartRowIndex} must be smaller than {B2: EndRowIndex} \n" );
  }
const strPocketTsv        = configs.getObject( "SelectConfC1" ); // NotRequired


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


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

/// get Sheet Properties

// request1, prepare
// Sheets for Developers > API v4
// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get
let request1Uri = "https://sheets.googleapis.com/v4/spreadsheets/" + strFileId;
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, try
const response1     = request1.get( 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" );
}

// parse response1
const response1Obj = JSON.parse( response1Body );
/* response sample
engine.log( response1Body ); // 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/17rwXXXXxS34yyyyYr-KzzzzX0_iWWWWOfHdxxxxRCLM/edit"
}
*/

if( strSheetId === "" ){
  /// get SheetId from SheetName
  for( let i = 0; i < response1Obj.sheets.length; i++ ){
    engine.log( " - " + response1Obj.sheets[i].properties.title );
    if( response1Obj.sheets[i].properties.title === strSheetName ){
      strSheetId = response1Obj.sheets[i].properties.sheetId;
      engine.log( " AutomatedTask SheetName '" + strSheetName +
                    "': SheetId " + strSheetId );
      break;
    }
  }
  if( strSheetId === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A3 Sheet Name} does not exist \n" );
  }
}else{
  const numSheetId = parseInt( strSheetId, 10 );
  /// get SheetName from SheetId
  for( let i = 0; i < response1Obj.sheets.length; i++ ){
    engine.log( " - " + response1Obj.sheets[i].properties.sheetId );
    if( response1Obj.sheets[i].properties.sheetId === numSheetId ){
      strSheetName = response1Obj.sheets[i].properties.title;
      engine.log( " AutomatedTask SheetId '" + strSheetId +
                    "': SheetName " + strSheetName );
      break;
    }
  }
}


/// backup RowRange

let strTsv = "";
if( strPocketTsv    !== null ){
  const strA1Notation = strSheetName + "!" + (numRowRangeStart+1) + ":" + numRowRangeEnd;
  engine.log( " AutomatedTask Backup Range: " + strA1Notation );

  /// Sheets for Developers > API v4
  /// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet

  // request2, prepare
  let request2Uri = "https://sheets.googleapis.com/v4/spreadsheets/" + strFileId + "/values:batchGet";
  let request2    = httpClient.begin(); // HttpRequestWrapper
      request2    = request2.authSetting( strAuthzSetting ); // with "Authorization: Bearer XX"
      // https://questetra.zendesk.com/hc/en-us/articles/360024574471-R2300#HttpRequestWrapper
      request2    = request2.queryParam( "majorDimension", "ROWS" );
      request2    = request2.queryParam( "valueRenderOption", "FORMATTED_VALUE" ); 
                   // Even if formatted as currency, return "$1.23" not "1.23".
      request2    = request2.queryParam( "dateTimeRenderOption", "FORMATTED_STRING" ); 
                   // Date as strings (the spreadsheet locale) not SERIAL_NUMBER
      request2    = request2.queryParam( "ranges", strA1Notation );

  // request2, try
  const response2     = request2.get( request2Uri ); // HttpResponseWrapper
  engine.log( " AutomatedTask ApiRequest2 Start: " + request2Uri );
  const response2Code = response2.getStatusCode() + "";
  const response2Body = response2.getResponseAsString() + "";
  engine.log( " AutomatedTask ApiResponse Status: " + response2Code );
  if( response2Code !== "200"){
    throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                      response2Code + "\n" + response2Body + "\n" );
  }

  // response2, parse
  const response2Obj = JSON.parse( response2Body );
  /* response sample
  engine.log( response2Body ); // debug
  {
    "spreadsheetId": "17rwXXXXxS34yyyyYr-KzzzzX0_iWWWWOfHdxxxxRCLM",
    "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"
          ]
        ]
      }
    ]
  }
  */

  engine.log( " AutomatedTask ApiResponse: range: " + response2Obj.valueRanges[0].range );
  let numTsvWidth = 0;
  for( let j = 0; j < response2Obj.valueRanges[0].values.length; j++ ){
    if( numTsvWidth < response2Obj.valueRanges[0].values[j].length ){
      numTsvWidth   = response2Obj.valueRanges[0].values[j].length;
    }
  }
  engine.log( " AutomatedTask ApiResponse: tmpTsv width: " + numTsvWidth );
  for( let j = 0; j < response2Obj.valueRanges[0].values.length; j++ ){
    for( let k = 0; k < numTsvWidth; k++ ){
      if( k < response2Obj.valueRanges[0].values[j].length ){
        strTsv += response2Obj.valueRanges[0].values[j][k];
      }
      if( k != response2Obj.valueRanges[0].values[j].length - 1 ){
        strTsv += "\t";
      }
    }
    if( j != response2Obj.valueRanges[0].values.length - 1 ){
      strTsv += "\n";
    }
  }
}


/// delete RowRange
// Google Sheets for Developers > Sheets API
// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#DeleteRangeRequest

// request3, prepare
let request3Obj = {};
    request3Obj.requests = [];
    request3Obj.requests[0] = {};
    request3Obj.requests[0].deleteRange = {};
    request3Obj.requests[0].deleteRange.shiftDimension = "ROWS";
    request3Obj.requests[0].deleteRange.range = {};
    request3Obj.requests[0].deleteRange.range.sheetId = (strSheetId - 0);
    request3Obj.requests[0].deleteRange.range.startRowIndex = numRowRangeStart;
    request3Obj.requests[0].deleteRange.range.endRowIndex   = numRowRangeEnd; 
let request3Uri = "https://sheets.googleapis.com/v4/spreadsheets/" + strFileId + ":batchUpdate";
let request3    = httpClient.begin(); // HttpRequestWrapper
    request3    = request3.authSetting( strAuthzSetting ); // with "Authorization: Bearer XX"
    // https://questetra.zendesk.com/hc/en-us/articles/360024574471-R2300#HttpRequestWrapper
    request3 = request3.body( JSON.stringify( request3Obj ), "application/json" );
engine.log( " AutomatedTask ApiRequest3 Prepared" );

// request3, try
const response3     = request3.post( request3Uri );       // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest3 Start: " + request3Uri );
const response3Code = response3.getStatusCode() + ""; // (primitive string)
const response3Body = response3.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse3 Status: " + response3Code );
if( response3Code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response3Code + "\n" + response3Body + "\n" );
}
/* response sample
engine.log( response3Body ); // debug
{
  "spreadsheetId": "1Wfruxxxxxl6FmwyyyyyWzYLxzzzzzLQmiPxxxxxR_4I",
  "replies": [
    {}
  ]
}
*/


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


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


/*
Notes:
- When the process reaches this automated step, rows in Google Sheets will be deleted.
    - Automates deletions by using the wider API Scope (strong privilege).
- The range is specified by the zero-based indexes of start index and end index.
    - If to delete all cell, specify "0" for the start index and "1000" for the end index.
    - The index is evaluated by JavaScript `parseInt ()`.
        - When specifying with numeric data, be careful not to mix digit delimiters.
        - https://developer.mozilla.org/docs/Web/JavaScript/Reference/Global_Objects/parseInt
    - Index values outside the range will be corrected.
- Spreadsheets are specified by ID, such as by referencing a URL. (SPREADSHEETID)
    - https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=SHEETID
- It is also possible to import (back up) deleted data as Workflow data.
    - The TSV string has a uniform number of columns (rectangular data) in each row.
    - There is no line feed code at the end of the TSV string.

APPENDIX-en
- The backup lines and the deleted lines may be out of alignment if edited at the same time.
- To restore the spreadsheet to a previous version, go to the "Change History" of the file.
    - "Find what's changed in a file" (Google file)
    - "The revisions for your file may occasionally be merged to save storage space."
    - https://support.google.com/docs/answer/190843
- Setting example of "HTTP Authentication" (OAuth2)
    - Authorization Endpoint URL:
        - https://accounts.google.com/o/oauth2/v2/auth?access_type=offline&prompt=consent
    - Token Endpoint URL:
        - https://oauth2.googleapis.com/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
    - (OFFICIAL) https://developers.google.com/identity/protocols/oauth2/web-server#httprest

Notes-ja:
- 案件が自動工程に到達した際、Google スプレッドシートの一部の行が自動削除されます。
    - 広範な API Scope（強い権限）を使った削除作業の自動化。
- 削除範囲は開始行と終了行のインデックス値で指定します。（ゼロ始まり）
    - 全データを削除したい場合、開始Indexに「0」、終了Indexに「1000」を指定します。
    - インデックスは JavaScript `parseInt()` にて評価されます。
        - 数値型データで指定する場合、桁区切り文字の混入ケースに注意が必要です
        - https://developer.mozilla.org/docs/Web/JavaScript/Reference/Global_Objects/parseInt
    - 範囲外のインデックス値は補正されます。
- スプレッドシートは、URLを参照するなどして ID で指定します。(SPREADSHEETID)
    - https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=SHEETID
- 削除されたデータをWorkflowデータとして取り込む（バックアップする）ことも可能です。
    - TSV文字列は、いずれの行も均一の列数（矩形データ）となります。
    - TSV文字列の末尾に改行コードはありません。

APPENDIX-ja
- 同時編集により、バックアップ行と削除行がズレる可能性があります。
- スプレッドシートを過去バージョンに復元したい場合、ファイルの "変更履歴" にアクセスします。
    - (Google 形式の) "ファイルの変更内容を確認する"
    - "容量節約のため、ファイルの版が統合されることがあります。"
    - https://support.google.com/docs/answer/190843
- "HTTP認証"（OAuth2）の設定例
    - Authorization Endpoint URL:
        - https://accounts.google.com/o/oauth2/v2/auth?access_type=offline&prompt=consent
    - Token Endpoint URL:
        - https://oauth2.googleapis.com/token
    - Scope:
        - https://www.googleapis.com/auth/spreadsheets
    - Client ID, Consumer Secret:
        - ( from https://console.developers.google.com/ )
        - Redirect URLs: https://s.questetra.net/oauth2callback
    - (OFFICIAL) https://developers.google.com/identity/protocols/oauth2/web-server#httprest
*/