2023-08-22 / Add-ons / , ,
Google Sheets: Row Range, Delete
Google Sheets: Row Range, Delete
Deletes rows in the specified range. Existing cells will be shifted upward. Both the start and end rows are specified by zero-based index. For example, “startIndex: 0, endIndex: 1” means only the first line. (End index is exclusive)
Configs
  • U: Select HTTP_Authz Setting *
  • A1: Set SPREADSHEET-ID in Drive *#{EL}
  • A2: Set SHEET-ID in SPREADSHEET#{EL}
  • A3: Or, Set SHEET-NAME in SPREADSHEET#{EL}
  • B1: Set Range (StartRowIndex) *#{EL}
  • B2: Set Range (EndRowIndex) *#{EL}
  • C1: If to Backup TSV, Select STRING that stores TSV (update)
Script (click to open)
// 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
*/

Download

2021-06-02 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/google-sheets-row-range-delete-2022/
The Addon-import feature is available with Professional edition.
Freely modifiable JavaScript (ECMAScript) code. No warranty of any kind.

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.
  • Spreadsheets are specified by ID, such as by referencing a URL. (SPREADSHEETID)
  • 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.

Capture

Deletes rows in the specified range. Existing cells will be shifted upward. Both the start and end rows are specified by zero-based index. For example, "startIndex: 0, endIndex: 1" means only the first line. (End index is exclusive)

Appendix

See also

Google Sheets: Sheet, Append TSV

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d