Google Sheets: ValueRanges, Export as TSV
Google Sheets: ValueRanges, Export as TSV
Stores a specified rectangular range of data as a TSV String. Empty cells are regarded as an empty string. Exporting two ranges at the same time is also supported. For example, the heading line and the 10th and subsequent lines can be exported at the same time.
Configs
  • U: Select HTTP_Authz Setting *
  • A1: Set FILE-ID in Drive *#{EL}
  • B1: Set ValueRange (e.g. “Sheet2!1:1”)#{EL}
  • C1: Select STRING that stores TSV (update)
  • B2: Set ValueRange (e.g. “Sheet2!10:15”)#{EL}
  • C2: 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 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
*/

Download

2021-04-23 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/google-sheets-valueranges-tsv-export-2021/
The Addon-import feature is available with Professional edition.

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 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.

Capture

Stores the values in the specified rectangular range as TSV String. Empty cells are regarded as the empty string. Two range export are also supported. For example, the heading line and the 10th and subsequent lines can be exported at the same time.
Stores the values in the specified rectangular range as TSV String. Empty cells are regarded as the empty string. Two range export are also supported. For example, the heading line and the 10th and subsequent lines can be exported at the same time.

Appendix

See also

Leave a Reply

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

%d bloggers like this: