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

Google Sheets: Update Row

この工程は、Google スプレッドシートの指定した行のデータを更新します。

Auto Step icon
Basic Configs
工程名
メモ
Configs for this Auto Step
conf_OAuth2
C1: OAuth2 設定 *
conf_SheetId
C2: 入力先のスプレッドシート ID *
conf_SheetTitle
C3: 入力先のシートタイトル *
conf_RowNo
C4: 更新する行 (例 “1”, “11”) *
conf_Range
C5: 更新する列範囲 (例 “A:J”,”K:L”) (最大 10 列) *
conf_Column1
C6_1: 列範囲のうち 1 列目の更新後の値#{EL}
conf_Column2
C6_1: 列範囲のうち 2 列目の更新後の値#{EL}
conf_Column3
C6_3: 列範囲のうち 3 列目の更新後の値#{EL}
conf_Column4
C6_4: 列範囲のうち 4 列目の更新後の値#{EL}
conf_Column5
C6_5: 列範囲のうち 5 列目の更新後の値#{EL}
conf_Column6
C6_6: 列範囲のうち 6 列目の更新後の値#{EL}
conf_Column7
C6_7: 列範囲のうち 7 列目の更新後の値#{EL}
conf_Column8
C6_8: 列範囲のうち 8 列目の更新後の値#{EL}
conf_Column9
C6_9: 列範囲のうち 9 列目の更新後の値#{EL}
conf_Column10
C6_10: 列範囲のうち 10 列目の更新後の値#{EL}

Notes

  • スプレッドシートの ファイルID は、URL に含まれていますhttps://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0

Capture

See also

Script (click to open)
  • 下記のスクリプトを記述した XML ファイルをダウンロードできます
    • google-sheets-row-update.xml (C) Questetra, Inc. (MIT License)
    • Professional をご利用であればファイルの内容を改変することでオリジナルのアドオンとして活用できます


// 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;
}

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