Google スプレッドシート: 行データ, 単一行TSVで更新

// GraalJS Script (engine type: 2)
/*
NOTES
- Refers and updates the first visible sheet if sheet name is not specified.
- If row deletion or sort is performed at the same time, different record may be updated.
- When the maximum row of the sheet is reached, an error will occur. (Not appended)
- If no data in column A, an error will occur. (Not appended)

- シート名が無指定の場合、先頭シート（表示されているもの）を参照・更新します。
- 同時に、行削除や並替処理がなされた場合、異なるレコードが更新されてしまう可能性があります。
- シートの最大行に達した際は、エラー終了します（追記されません）
- A列に1つのデータも存在しない場合は、エラー終了します（追記されません）
*/

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

//// == Config Retrieving / 工程コンフィグの参照 ==
const strOauthSetting       = configs.get( "OAuth2ConfA" );   // required
const strSingleTsv          = configs.get( "StrConfB" ) + ""; // required
const strDocId              = configs.get( "StrConfC" ) + ""; // required
let   strSheetName          = configs.get( "StrConfD" ) + ""; // required

if( strSingleTsv === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {B TSV} not specified \n" );
}
if( strDocId === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {C Spreadsheet ID} not specified \n" );
}
if( strSheetName === "" ){
  engine.log( " AutomatedTask ConfigWarning:" +
              " Config {E Sheet Name} is empty, the first visible sheet is refered" );
}


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


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

//// Check Tsv Dataset
const arrTsvCells = strSingleTsv.split("\t");
engine.log( " AutomatedTask Singleline TSV Loaded: Number of Columns " + arrTsvCells.length );


//// Get CellValues from the Sheet (Api Request 1)

// prepare request1
// ref) https://developers.google.com
//            /sheets/api/reference/rest/v4/spreadsheets.values/get
const token  = httpClient.getOAuth2Token( strOauthSetting );
let strRange = strSheetName ? (strSheetName + "!A:A") : "A:A";
engine.log( " AutomatedTask Range to Get: " + strRange );
let getUri1  = "https://sheets.googleapis.com/v4/spreadsheets/" +
                strDocId + "/values/" + strRange;
let request1 = httpClient.begin(); // HttpRequestWrapper
    request1 = request1.bearer( token );
    request1 = request1.queryParam( "majorDimension", "ROWS" ); 
    request1 = request1.queryParam( "valueRenderOption", "FORMATTED_VALUE" ); 
               // 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
engine.log( " AutomatedTask ApiRequest1 Prepared" );

// request1
const response1     = request1.get( getUri1 ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest1 Start: " + getUri1 );
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
/* engine.log( response2Body ); // debug
response sample
{
  "range": "'country-code'!A1:A1000",
  "majorDimension": "ROWS",
  "values": [
    [
      "Afghanistan"
    ],
    [
      "Aland Islands"
    ],
    [
      "Zimbabwe"
    ]
  ]
}
*/
const response1Obj = JSON.parse( response1Body );

if(! 'values' in response1Obj){
  throw new Error( "\n AutomatedTask UnexpectedSheetError: " +
                    "No Value in " + strRange + "\n" );
}
engine.log( " AutomatedTask ApiResponse #of Values: " + response1Obj.values.length );
let arrColAValues  = new Array( response1Obj.values.length );
for( let i = 0; i < response1Obj.values.length; i++ ){
  arrColAValues[i] = response1Obj.values[i] ? response1Obj.values[i][0] : "";
}


//// Search "arrTsvCells[0]" in "arrColAValues"
let numMatched = arrColAValues.length; // NoMatch Case
for( let i = 0; i < arrColAValues.length; i++ ){
  if( arrColAValues[i] === arrTsvCells[0] ){
    numMatched = i;  // Match Case
    break;
  }
}


//// Update Values of the Sheet (Api Request 2)

// prepare request2
// ref) https://developers.google.com
//            /sheets/api/reference/rest/v4/spreadsheets.values/update
let strRange2 = strSheetName ?
                strSheetName + "!" + (numMatched + 1) + ":" + (numMatched + 1) :
                (numMatched + 1) + ":" + (numMatched + 1);
engine.log( " AutomatedTask Range to Update: " + strRange2 );
let request2Obj = {};
    request2Obj.values = [];
    request2Obj.values[0] = strSingleTsv.split("\t");
let request2 = httpClient.begin(); // HttpRequestWrapper
    request2 = request2.bearer( token );
    request2 = request2.queryParam( "valueInputOption", "USER_ENTERED" ); 
    request2 = request2.body( JSON.stringify( request2Obj ), "application/json" );
engine.log( " AutomatedTask ApiRequest2 Prepared" );

// request2
let   putUri2       = "https://sheets.googleapis.com/v4/spreadsheets/" +
                       strDocId + "/values/" + strRange2;
const response2     = request2.put( putUri2 ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest2 Start: " + putUri2 );
const response2Code = response2.getStatusCode() + ""; // (primitive string)
const response2Body = response2.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + response2Code );
if( response2Code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response2Code + "\n" + response2Body + "\n" );
}


//// == Data Updating / ワークフローデータへの代入 ==
// (nothing)


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



/*
APPENDIX
- "SpreadsheetID" and "SheetID" in URL
    - https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=SHEETID
- To Get "ClientId" and "Secret"
    - Access to https://console.developers.google.com/apis/credentials
- This request requires authorization with at least one of the following scopes:
    - https://www.googleapis.com/auth/drive
    - https://www.googleapis.com/auth/drive.file
    - https://www.googleapis.com/auth/spreadsheets
- OAuth Setting sample
    - "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
- Error Log sample (wrong SheetName)
    - "message": "Unable to parse range: NonExistName!A:A",
    - "status": "INVALID_ARGUMENT"
- Error Log sample (Need to expand editing area)
    "message": "Range ('Sheet1'!1001) exceeds grid limits. Max rows: 1000, max columns: 26",
*/