Google Sheets: Values, Export as TSV

Google Sheets: Values, Export as TSV

Exports the values in the rectangular range as TSV text, which has the same number of tab delimiters on each line. Empty cells are regarded as the null string. Two range export are also supported: e.g. Freezed headings and recent data.

2019-10-25 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/google-sheets-values-export-as-tsv/

Configs
  • A: Select OAuth2 Config Name (at [OAuth 2.0 Setting]) *
  • B: Set Document-ID of Spreadsheet File (44 chars in File URI) * #{EL}
  • C1: Set ValueRange (e.g. “Sheet1!A1:B10” cf.’A1 Notation’) * #{EL}
  • D1: Select STRING DATA for TSV Text (update) *
  • C2: Set ValueRange (e.g. “Sheet1!D1:E10”) #{EL}
  • D2: Select STRING DATA for TSV Text (update)
Script
// (c) 2019, Questetra, Inc. (the MIT License)

//// == OAuth2 Setting example ==
// 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:
//  ( from https://console.developers.google.com/ )
// Consumer Secret:
//  ( from https://console.developers.google.com/ )
//  *Redirect URL of Webapp OAuth-Client-ID: "https://s.questetra.net/oauth2callback"

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

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

//// == Config Retrieving / 工程コンフィグの参照 ==
const oauth2      = configs.get( "conf_OAuth2"  ) + "";     // required
const documentId  = configs.get( "conf_DocumentId" ) + "";  // required
const valueRange1 = configs.get( "conf_ValueRange1" ) + ""; // required
const dataIdD1    = configs.get( "conf_DataIdD1" ) + "";    // required
const valueRange2 = configs.get( "conf_ValueRange2" ) + ""; // not required
const dataIdD2    = configs.get( "conf_DataIdD2" ) + "";    // not required
// 'java.lang.String' (String Obj) to javascript primitive 'string'

engine.log( " AutomatedTask Config: Document ID: " + documentId );
engine.log( " AutomatedTask Config: ValueRange1: " + valueRange1 );
engine.log( " AutomatedTask Config: ValueRange2: " + valueRange2 );


//// == Data Retrieving / ワークフローデータの参照 ==
// (nothing)


//// == Calculating / 演算 ==
/// obtain OAuth2 Access Token
const token   = httpClient.getOAuth2Token( oauth2 );

/// get one or two ranges of values via Google Sheets API v4
// https://developers.google.com/sheets/api/reference/rest
//  /v4/spreadsheets.values/batchGet
let apiRequest = httpClient.begin(); // HttpRequestWrapper
    apiRequest = apiRequest.bearer( token );
    apiRequest = apiRequest.queryParam( "ranges", valueRange1 ); 
    if( valueRange2 !== ""){
      apiRequest = apiRequest.queryParam( "ranges", valueRange2 ); 
    }
    apiRequest = apiRequest.queryParam( "majorDimension", "ROWS" ); 
    apiRequest = apiRequest.queryParam( "valueRenderOption", "FORMATTED_VALUE" ); 
                 // If formatted as currency, return "$1.23" not "1.23".
    apiRequest = apiRequest.queryParam( "dateTimeRenderOption", "FORMATTED_STRING" ); 
                 // Date as strings (the spreadsheet locale) not SERIAL_NUMBER
const apiUri = "https://sheets.googleapis.com/v4/spreadsheets/" +
                documentId + "/values:batchGet";
engine.log( " AutomatedTask Trying: GET " + apiUri );
const response = apiRequest.get( apiUri );
const responseCode = response.getStatusCode() + "";
engine.log( " AutomatedTask ApiResponse: Status " + responseCode );
if( responseCode !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
         responseCode + "\n" + response.getResponseAsString() + "\n" );
}
const responseStr = response.getResponseAsString() + "";
const responseObj = JSON.parse( responseStr );
engine.log( responseStr );

let strTsv1 = "";
  let numMaxLength1 = 0;
  for( let j = 0; j < responseObj.valueRanges[0].values.length; j++ ){
    if( numMaxLength1 < responseObj.valueRanges[0].values[j].length ){
      numMaxLength1 = responseObj.valueRanges[0].values[j].length;
    }
  }
  for( let j = 0; j < responseObj.valueRanges[0].values.length; j++ ){
    for( let k = 0; k < numMaxLength1; k++ ){
      if( k < responseObj.valueRanges[0].values[j].length ){
        strTsv1 += responseObj.valueRanges[0].values[j][k];
      }
      if( k != responseObj.valueRanges[0].values[j].length - 1 ){
        strTsv1 += "\t";
      }
    }
    if( j != responseObj.valueRanges[0].values.length - 1 ){
      strTsv1 += "\n";
    }
  }
engine.log( " AutomatedTask Response of ValueRange1: \n" + strTsv1 );

let strTsv2 = "";
if( valueRange2 !== ""){
  let numMaxLength2 = 0;
  for( let j = 0; j < responseObj.valueRanges[1].values.length; j++ ){
    if( numMaxLength2 < responseObj.valueRanges[1].values[j].length ){
      numMaxLength2 = responseObj.valueRanges[1].values[j].length;
    }
  }
  for( let j = 0; j < responseObj.valueRanges[1].values.length; j++ ){
    for( let k = 0; k < numMaxLength2; k++ ){
      if( k < responseObj.valueRanges[1].values[j].length ){
        strTsv2 += responseObj.valueRanges[1].values[j][k];
      }
      if( k != responseObj.valueRanges[1].values[j].length - 1 ){
        strTsv2 += "\t";
      }
    }
    if( j != responseObj.valueRanges[1].values.length - 1 ){
      strTsv2 += "\n";
    }
  }
  engine.log( " AutomatedTask Response of ValueRange2: \n" + strTsv2 );
}


//// == Data Updating / ワークフローデータへの代入 ==
engine.setDataByNumber( dataIdD1, strTsv1 );
if ( dataIdD2 !== "" ){ 
  engine.setDataByNumber( dataIdD2, strTsv2 );
}


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

Download

Capture

Google Sheets: Values, Export as TSV

Notes

  1. Document ID is contained in the URL. https://docs.google.com/spreadsheets/d/(Sheet ID)/edit#gid=0
  2. Formats on the spreadsheet (decimal separators, rounding indications, suffixes) are directly reflected in the TSV text.
  3. If you want to check the “tab character” in the human step, you need to make permission Editable.
  4. Even if a range is specified, columns and rows with no data are ignored.
  5. How to specify the range depends on Google API A1 notation.
    1. “Sheet1!A1:B2” refers to the first two cells in the top two rows of Sheet1.
    2. “Sheet1!A:A” refers to all the cells in the first column of Sheet1.
    3. “Sheet1!1:2” refers to the all the cells in the first two rows of Sheet1.
    4. “Sheet1!A5:A” refers to all the cells of the first column of Sheet 1, from row 5 onward.
    5. “A1:B2” refers to the first two cells in the top two rows of the first visible sheet.
    6. “Sheet1” refers to all the cells in Sheet1.

See also

1 thought on “Google Sheets: Values, Export as TSV”

  1. Pingback: Get Google Sheets Values as TSV – Questetra Support

Leave a Reply

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

%d bloggers like this: