Google スプレッドシート: 行追加

// Google Sheets Append Cells (ver. 20171031)
// (c) 2017, Questetra, Inc. (the MIT License)
// by spreadsheets.batchUpdate [AppendCellsRequest] (not by spreadsheets.values.append)
// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#appendcellsrequest

// OAuth2 config sample at [OAuth 2.0 Setting]
// - 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
// - Consumer Key: (Get by Google Developers Console)
// - Consumer Secret: (Get by Google Developers Console)


//// == Config Retrieving / 工程コンフィグの参照 ==
main();
function main(){
  const oauth2 = configs.get( "conf_OAuth2" ) + "";
  const spreadsheetId = configs.get( "conf_DataIdW" ) + "";
  const sheetName = configs.get( "conf_DataIdX" ) + "";
  const dataIdA = configs.get( "conf_DataIdA" ) + "";
  const dataIdB = configs.get( "conf_DataIdB" ) + "";
  const dataIdC = configs.get( "conf_DataIdC" ) + "";
  const dataIdD = configs.get( "conf_DataIdD" ) + "";
  const dataIdE = configs.get( "conf_DataIdE" ) + "";
  const dataIdF = configs.get( "conf_DataIdF" ) + "";
  const dataIdG = configs.get( "conf_DataIdG" ) + "";
  const dataIdH = configs.get( "conf_DataIdH" ) + "";
  const dataIdI = configs.get( "conf_DataIdI" ) + "";
  const dataIdJ = configs.get( "conf_DataIdJ" ) + "";
  // convert 'java.lang.String' to 'javascript string'

  //// == Calculating / 演算 ==
  // Request QUERY (?a=b)
  // (no set)
  let requestObj = {};
    requestObj.requests = [];
    requestObj.requests[0] = {};
    requestObj.requests[0].appendCells = {};
    requestObj.requests[0].appendCells.sheetId = -1;
    requestObj.requests[0].appendCells.fields = "*";
    requestObj.requests[0].appendCells.rows = [];
    requestObj.requests[0].appendCells.rows[0] = {};
    requestObj.requests[0].appendCells.rows[0].values = [];

    requestObj.requests[0].appendCells.rows[0].values[0] = cellDataObject( dataIdA );
    requestObj.requests[0].appendCells.rows[0].values[1] = cellDataObject( dataIdB );
    requestObj.requests[0].appendCells.rows[0].values[2] = cellDataObject( dataIdC );
    requestObj.requests[0].appendCells.rows[0].values[3] = cellDataObject( dataIdD );
    requestObj.requests[0].appendCells.rows[0].values[4] = cellDataObject( dataIdE );
    requestObj.requests[0].appendCells.rows[0].values[5] = cellDataObject( dataIdF );
    requestObj.requests[0].appendCells.rows[0].values[6] = cellDataObject( dataIdG );
    requestObj.requests[0].appendCells.rows[0].values[7] = cellDataObject( dataIdH );
    requestObj.requests[0].appendCells.rows[0].values[8] = cellDataObject( dataIdI );
    requestObj.requests[0].appendCells.rows[0].values[9] = cellDataObject( dataIdJ );
    
    let count = 0;
    let flag = 1;
    for(let i = 9; i > -1; i--){
      if(requestObj.requests[0].appendCells.rows[0].values[i].userEnteredValue.stringValue == ""){
        count++;
        if(flag){
          requestObj.requests[0].appendCells.rows[0].values[i] = null;
        }
      }else{
        flag = 0;
      }
    }
    if(count == 10){
      throw "No Data to add is set.";
    }
  // Get OAuth2 Token
  const token  = httpClient.getOAuth2Token( oauth2 );

  // Access to the API 1st(Get GID)
  let getgid = httpClient.begin()
    .bearer( token )
    .get ("https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId);
  const getRes = JSON.parse(getgid.getResponseAsString());
  const sheetArray = getRes.sheets;
  if(sheetArray == [] || sheetArray == null){
    throw "Can't get sheet information";
  }
  for (var i = 0;i < sheetArray.length;i++){
    if(sheetArray[i].properties.title === sheetName){
      requestObj.requests[0].appendCells.sheetId = sheetArray[i].properties.sheetId;
    }
  }
  if (requestObj.requests[0].appendCells.sheetId == -1){
    throw "Sheet '" + sheetName + "' doesn't exist";
  }
  // Set Url (https://example.com/abc/def/)
  const apiUri = "https://sheets.googleapis.com/v4/spreadsheets/"+ spreadsheetId + ":batchUpdate";
  // Access to the API 2nd(POST)
  let response = httpClient.begin()
    .bearer( token )
    .body( JSON.stringify( requestObj ), "application/json" )
    .post( apiUri ); // HttpResponseWrapper
  
  const httpStatus = response.getStatusCode() + "";
  let accessLog = "---POST request--- " + httpStatus + "\n";
  accessLog += response.getResponseAsString() + "\n";
  if( response.getStatusCode() != 200 ){
      throw accessLog;
    }
  //var responseObj = JSON.parse( response.getResponseAsString() );

  // Retrieve Properties from Response-JSON
  // (no action)

  // Error Handling - https://stripe.com/docs/api#errors
  // (no action)

  //// == Output Log / ログ出力 ==
  engine.log(accessLog);
}
// Request BODY (JSON, Form Parameters, etc)
function cellDataObject( data ){
  let dataObj = {};
  dataObj.userEnteredValue = {};
  if( data === "" || data === null){
    dataObj.userEnteredValue.stringValue = "";
    return dataObj;
  }else{ // for "STRING" 
    if(data.length > 50000){
      throw "Can't set text over 50,000 character.";
    }
    dataObj.userEnteredValue.stringValue = data + "";
    return dataObj;
  }
}