- 下記のスクリプトを記述した XML ファイルをダウンロードできます
// 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;
}