Google スプレッドシート: 行更新

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

const COLUMN_MAX_NUM = 10;

main();
function main(){

  //// == 工程コンフィグの参照 / Config Retrieving ==
  const oauth = configs.get( "conf_OAuth2" );
  const sheetId = retrieveStringData( "conf_SheetId", "Target Spreadsheet ID" );
  const sheetTitle = retrieveStringData( "conf_SheetTitle", "Target Sheet Title" );
  const rowNo = retrieveRowNo();
  const range = configs.get( "conf_Range" );
  checkRowNoAndRange( rowNo, range );
  const rangeArr = range.split(':');
  const dataArray = retrieveRowValues(rangeArr);
  
  //// == 演算 / Calculating ==
  updateRowData( oauth, sheetId, sheetTitle, rowNo, rangeArr, dataArray  );

}


/**
 * 文字列データを 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;
}


/**
  * config から行番号 を読み出す
  * @return {String} rowNo  行番号
  */
function retrieveRowNo() {
  const rowNoDef = configs.getObject( "conf_RowNo" );
  return engine.findData( rowNoDef );
}


/**
  * 列範囲 の最初の列と最終の列から 更新列数を読み出し、更新後の値を列数分だけ格納する
  * @param {Array<String>} rangeArr 更新する列範囲の最初の列と最終の列が格納された配列
  * @return {Array<String>} dataArray 更新後の値の配列
  */
function retrieveRowValues(rangeArr) {
  const dataArray = [];
  let columnNum = lettersToNumber(rangeArr[1]) - lettersToNumber(rangeArr[0]) + 1;
  if (columnNum <= 0) {
    throw "Invalid Range.";
  }
  if (columnNum > COLUMN_MAX_NUM) {
    columnNum = COLUMN_MAX_NUM;
  }
  for (let i = 0; i < columnNum; i++) {    
    const value = configs.get(`conf_Column${i+1}`);
    if(value.length > 50000){
      throw "Can't set text over 50,000 characters.";
    }
    dataArray.push(value);
  }
  return dataArray;
}


/**
  * 行番号が空、不正な文字列であればエラーとする
  * 列範囲が不適切な文字列であればエラーとする
  * @param {String} rowNo  行番号
  * @param {String} range  列範囲
  */
function checkRowNoAndRange( rowNo, range ) {
  if ( rowNo === "" || rowNo === null ) {
    throw "Row number is empty.";
  }
  if ( !isValidNo(rowNo) ) {
    throw "Invalid Row number.";
  }
  if ( !isValidRange(range) ) {
    throw "Invalid Range.";
  }
}


/**
  * 行番号 が有効か（自然数か）を判定する
  * @param {String} noString  行番号 の文字列
  * @return {Boolean}  有効な 行番号 かどうか
  */
function isValidNo( noString ) {
  const idReg = new RegExp( '^[1-9][0-9]*$' );
  return idReg.test( noString );
}


/**
  * 列範囲 が有効かを判定する
  * @param {String} rangeString  列範囲 の文字列
  * @return {Boolean}  有効な 列範囲 かどうか
  */
function isValidRange( rangeString ) {
  const idReg = new RegExp( '^[A-Z]+:[A-Z]+$' );
  return idReg.test( rangeString );
}


/**
 * Google スプレッドシートの行データを更新
 * @param {String} oauth OAuth2 認証設定
 * @param {String} sheetId スプレッドシートの ID
 * @param {String} sheetTitle シート名
 * @param {String} rowNo 更新する行
 * @param {Array<String>} rangeArr 更新する列範囲の最初の列と最終の列が格納された配列
 * @param {Array<String>} dataArray 更新後の値の配列
 */
function updateRowData( oauth, sheetId, sheetTitle, rowNo, rangeArr, dataArray  ) {

  const enSheetId = encodeURIComponent(sheetId);
  const enSheetTitle = encodeURIComponent(sheetTitle);
  
  const requestObj = buildRequestObj(dataArray);
  
  // Method: spreadsheets.values.update
  const uri = `https://sheets.googleapis.com/v4/spreadsheets/${enSheetId}/values/${enSheetTitle}!${rangeArr[0]}${rowNo}:${rangeArr[1]}${rowNo}`;
  const response = httpClient.begin()
    .authSetting( oauth )
    .queryParam( "valueInputOption", "RAW" )
    .body( JSON.stringify( requestObj ), "application/json" )
    .put( uri );
  const status = response.getStatusCode();
  const responseStr = response.getResponseAsString();
  if (status !== 200) {
    const accessLog = `---GET request--- ${status}\n${responseStr}`;
    engine.log( accessLog );
    throw `Failed to update. status:${status}`;
  }
}


  /**
  * configから更新情報を読み出し、JSON オブジェクトを返す
  * @param {Array<String>} dataArray 更新後の値の配列
  * @return {Object} requestObj  JSON オブジェクト
  */
function buildRequestObj( dataArray ) {
  const requestObj = {};
  requestObj.majorDimension = "ROWS";
  requestObj.values = [];
  requestObj.values.push(dataArray);
  return requestObj;
}


// convert A to 1, Z to 26, AA to 27
function lettersToNumber(letters){
  const chrs = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  const mode = chrs.length - 1;
  let number = 0;
  for(let p = 0; p < letters.length; p++){
    number = number * mode + chrs.indexOf(letters[p]);
  }
  return number;
}