Google スプレッドシート: 行更新 (Google Sheets: Update Row)
Google スプレッドシートの指定した行のデータを更新します。
Configs:共通設定
  • 工程名
  • メモ
Configs
  • C1: OAuth2 設定 *
  • C2: 入力先のスプレッドシート ID *
  • C3: 入力先のシートタイトル *
  • C4: 更新する行 (例 “1”, “11”) *
  • C5: 更新する列範囲 (例 “A:J”,”K:L”) (最大 10 列) *
  • C6_1: 列範囲のうち 1 列目の更新後の値#{EL}
  • C6_1: 列範囲のうち 2 列目の更新後の値#{EL}
  • C6_3: 列範囲のうち 3 列目の更新後の値#{EL}
  • C6_4: 列範囲のうち 4 列目の更新後の値#{EL}
  • C6_5: 列範囲のうち 5 列目の更新後の値#{EL}
  • C6_6: 列範囲のうち 6 列目の更新後の値#{EL}
  • C6_7: 列範囲のうち 7 列目の更新後の値#{EL}
  • C6_8: 列範囲のうち 8 列目の更新後の値#{EL}
  • C6_9: 列範囲のうち 9 列目の更新後の値#{EL}
  • C6_10: 列範囲のうち 10 列目の更新後の値#{EL}

Notes

  • スプレッドシートの ID は、URL に含まれています。https://docs.google.com/spreadsheets/d/(sheet ID)/edit#gid=0

Capture

Appendix

  • google-sheets-row-update.xml (C) Questetra, Inc. (MIT License)
    • Professional をご利用であればファイルの内容を改変することでオリジナルのアドオンとして活用できます

See also

Script (click to open)


// 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 = configs.get( "conf_SheetId" );
  const sheetTitle = configs.get( "conf_SheetTitle" );
  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 から行番号 を読み出す
  * @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;
}

%d人のブロガーが「いいね」をつけました。