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

// (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()" ////////