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

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

main();
function main(){
  //// == Config Retrieving / 工程コンフィグの参照 ==
  const oauth2 = configs.get( "conf_OAuth2" );
  const spreadsheetId = retrieveStringData( "conf_DataIdW", "Target Spreadsheet ID" );
  const sheetName = retrieveStringData( "conf_DataIdX", "Target Sheet Title" );
  const rowNumDef = configs.getObject( "conf_RowNum" );

  const columns = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J"];
  const dataArray = columns.map( i => configs.get( `conf_DataId${i}` ) );

  //// == Calculating / 演算 ==
  const row = buildRowObj(dataArray);
  const sheetId = getSheetId(oauth2, spreadsheetId, sheetName); // Access to the API 1st(Get GID)
  appendRow(oauth2, spreadsheetId, sheetId, row); // Access to the API 2nd(POST)
  if(rowNumDef === null) return;

  // only when rowNumDef is not null
  const rowNum = getLastRowNum(oauth2, spreadsheetId, sheetName); // Access to the API 3rd(GET)

  //// == ワークフローデータへの代入 / Data Updating ==
  engine.setData(rowNumDef, rowNum);
}

/**
 * 文字列データを config から読み出す。空であればエラー。
 * @param  {String} confName config 名
 * @param  {String} label エラーメッセージ用のラベル
 * @return {String} string 文字列データ
 */
function retrieveStringData( confName, label ){
  let string = configs.get( confName );
  const dataDef = configs.getObject( confName );
  if (dataDef !== null) {
    string = engine.findData(dataDef);
  }
  if (string === null || string === "") {
    throw `${label} is empty.`;
  }
  return string;
}

/**
 * 文字列データを所定の形式のセルオブジェクトに変換する。
 * @param  {String} data 文字列データ
 * @return {Object} cellObj 文字列データが格納されたセルオブジェクト
 */
function buildCellObj( data ){
  const cellObj = {};
  cellObj.userEnteredValue = {};
  if( data === "" || data === null){
    cellObj.userEnteredValue.stringValue = "";
    return cellObj;
  }else{ // for "STRING"
    if(data.length > 50000){
      throw "Can't set text over 50,000 characters.";
    }
    cellObj.userEnteredValue.stringValue = data;
    return cellObj;
  }
}

/**
 * 文字列データを所定の形式の行オブジェクトに変換する。
 * @param  {Array<String>} dataArray 文字列データの配列
 * @return {Object} rowObj 文字列データが格納された行オブジェクト
 */
function buildRowObj( dataArray ){
  const rowObj = {};
  rowObj.values = dataArray.map( data => buildCellObj( data ) );

  // 後ろから空白の要素数を数える
  let count = 0;
  for(let i = rowObj.values.length; i > 0; i--) {
    if(rowObj.values[i-1].userEnteredValue.stringValue === ""){
      count++;
    }else{ // 空白でなければ、ループから抜ける
      break;
    }
  }

  // 空白部分を削除
  if(count > 0){
    rowObj.values = rowObj.values.slice(0, -count);
  }

  // すべてが空白ならエラー
  if(rowObj.values.length === 0){
    throw "No Data to add is set.";
  }

  return rowObj;
}

/**
 * GET リクエストを送信し、シート ID (GID) を取得する。
 * @param {String} oauth2 OAuth2 設定名
 * @param {String} spreadsheetId スプレッドシート ID
 * @param {String} sheetName シート名
 * @return {Number} sheetId シート ID (GID)
 */
function getSheetId(oauth2, spreadsheetId, sheetName){
  const uri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(spreadsheetId)}?includeGridData=false`;
  const response = httpClient.begin()
    .authSetting(oauth2)
    .get(uri);
  const status = response.getStatusCode();
  const responseStr = response.getResponseAsString();
  if (status !== 200) {
    engine.log(responseStr);
    throw `Failed to get sheet information. status: ${status}`;
  }
  const jsonObj = JSON.parse(responseStr);
  engine.log(`SPREADSHEET (TITLE: ${jsonObj.properties.title}) has ${jsonObj.sheets.length} sheets`);
  for (let i = 0; i < jsonObj.sheets.length; i++) {
    if (jsonObj.sheets[i].properties.title === sheetName) {
      const sheetId = parseInt(jsonObj.sheets[i].properties.sheetId, 10);
      engine.log(`SHEET TITLE: ${sheetName} exists (id: ${sheetId})`);
      return sheetId;
    }
  }
  // error (not exist)
  throw `Sheet ${sheetName} does not exist`;
}

/**
 * 行追加のリクエストボディを生成する。
 * @param  {Number} sheetId シート ID (GID)
 * @return {Object} row 行オブジェクト
 */
function buildRequestObj(sheetId, row){
  const requestObj = {};
  requestObj.requests = [];
  requestObj.requests[0] = {};
  requestObj.requests[0].appendCells = {};
  requestObj.requests[0].appendCells.sheetId = sheetId;
  requestObj.requests[0].appendCells.fields = "*";
  requestObj.requests[0].appendCells.rows = [];
  requestObj.requests[0].appendCells.rows[0] = row;
  return requestObj;
}

/**
 * 行追加の POST リクエストを送信する。
 * @param {String} oauth2 OAuth2 設定名
 * @param {String} spreadsheetId スプレッドシート ID
 * @param {Number} sheetId シート ID (GID)
 * @param {Object} row 行オブジェクト
 */
function appendRow(oauth2, spreadsheetId, sheetId, row){
  const apiUri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(spreadsheetId)}:batchUpdate`;
  const requestObj = buildRequestObj(sheetId, row);
  const response = httpClient.begin()
    .authSetting( oauth2 )
    .body( JSON.stringify( requestObj ), "application/json" )
    .post( apiUri ); // HttpResponseWrapper
  const httpStatus = response.getStatusCode();
  if( httpStatus !== 200 ){
    engine.log(response.getResponseAsString());
    throw `Failed to append data. status: ${httpStatus}`;
  }
}

/**
 * GET リクエストを送信し、データが入力されている最終行の行番号を返す。
 * @param {String} oauth2 OAuth2 設定名
 * @param {String} spreadsheetId スプレッドシート ID
 * @param {String} sheetName シート名
 * @return {String} データが入力されている最終行の行番号
 */
function getLastRowNum(oauth2, spreadsheetId, sheetName){
  const apiUri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(spreadsheetId)}/values/${encodeURIComponent(sheetName)}`;
  const response = httpClient.begin()
    .authSetting( oauth2 )
    .queryParam( "majorDimension", "ROWS" )
    .get( apiUri ); // HttpResponseWrapper
  const httpStatus = response.getStatusCode();
  const responseStr = response.getResponseAsString();
  if( httpStatus !== 200 ){
    engine.log(responseStr);
    throw `Failed to get rows in the sheet. status: ${httpStatus}`;
  }
  const jsonObj = JSON.parse(responseStr);
  return jsonObj.values.length.toString();
}