
Google スプレッドシート: 行取得
この工程は、Google スプレッドシートから指定した行のデータを取得します。
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”) (最大 10 列) *
- conf_Column1
- C6_1: 列範囲のうち 1 列目の値を保存するデータ項目
- conf_Column2
- C6_2: 列範囲のうち 2 列目の値を保存するデータ項目
- conf_Column3
- C6_3: 列範囲のうち 3 列目の値を保存するデータ項目
- conf_Column4
- C6_4: 列範囲のうち 4 列目の値を保存するデータ項目
- conf_Column5
- C6_5: 列範囲のうち 5 列目の値を保存するデータ項目
- conf_Column6
- C6_6: 列範囲のうち 6 列目の値を保存するデータ項目
- conf_Column7
- C6_7: 列範囲のうち 7 列目の値を保存するデータ項目
- conf_Column8
- C6_8: 列範囲のうち 8 列目の値を保存するデータ項目
- conf_Column9
- C6_9: 列範囲のうち 9 列目の値を保存するデータ項目
- conf_Column10
- C6_10: 列範囲のうち 10 列目の値を保存するデータ項目
Notes
- スプレッドシートの ファイルID は、URL に含まれています https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0
Capture
See also
Script (click to open)
- 次のスクリプトが記述されている XML ファイルをダウンロードできます
- google-sheets-row-get.xml (C) Questetra, In. (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.readonly
// Consumer Key: (Get by Google Developers Console)
// Consumer Secret: (Get by Google Developers Console)
const COLUMN_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 columnDefList = [];
retrieveValueConfigs( columnDefList );
//// == 演算 / Calculating ==
const dataStringList = getRowData( oauth, sheetId, sheetTitle, rowNo, range );
//// == ワークフローデータへの代入 / Data Updating ==
setDataByLists( columnDefList, dataStringList );
}
/**
* config から行番号 を読み出す
* @return {String} rowNo 行番号
*/
function retrieveRowNo() {
const rowNoDef = configs.getObject( "conf_RowNo" );
let rowNo = configs.get( "conf_RowNo" );
if ( rowNoDef !== null ) {
rowNo = engine.findData( rowNoDef );
}
return rowNo;
}
/**
* 行番号が空、不正な文字列であればエラーとする
* 列範囲が不適切な文字列であればエラーとする
* @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 );
}
/**
* config の列の値を格納するデータ項目の情報を読み出し、配列に格納する
* 以下の場合はエラーとする
* 1. 値を保存するデータ項目が重複して設定されている
* 2. 列の値を格納するデータ項目が一つも設定されていない
* @param {Array<ProcessDataDefinitionView>} valueDefList 列の値を格納するデータ項目の ProcessDataDefinitionView を格納する配列
*/
function retrieveValueConfigs( columnDefList ) {
const dataItemNumList = []; // データ項目の重複確認用
for (let i = 0; i < COLUMN_NUM; i++) {
const columnConfigName = `conf_Column${i+1}`;
const columnValueDef = configs.getObject( columnConfigName );
columnDefList.push( columnValueDef );
if ( columnValueDef !== null ) {
const dataItemNum = configs.get( columnConfigName ); // データ項目の重複確認用
if ( dataItemNumList.indexOf( dataItemNum ) !== -1 ) { // 既に指定されているデータ項目
throw "The same data item is set multiple times.";
}
dataItemNumList.push( dataItemNum ); // データ項目の重複確認用
}
}
if ( columnDefList.filter( v => v !== null ).length === 0 ) { // 列の値を格納するデータ項目が一つも設定されていない
throw "No Data Item is set.";
}
}
/**
* Google スプレッドシートの行データを取得
* @param {String} oauth OAuth2 認証設定
* @param {String} sheetId スプレッドシートの ID
* @param {String} sheetTitle シート名
* @param {String} rowNo 取得する行
* @param {String} range 取得する列範囲
* @return {Array<String>} jsonObj.values[0] 取得した行データ を格納した配列
*/
function getRowData( oauth, sheetId, sheetTitle, rowNo, range ) {
const rangeArr = range.split(':');
const enSheetId = encodeURIComponent(sheetId);
const enSheetTitle = encodeURIComponent(sheetTitle);
// Method: spreadsheets.values.get
const uri = `https://sheets.googleapis.com/v4/spreadsheets/${enSheetId}/values/${enSheetTitle}!${rangeArr[0]}${rowNo}:${rangeArr[1]}${rowNo}`;
const response = httpClient.begin()
.authSetting( oauth )
.queryParam( "valueRenderOption", "UNFORMATTED_VALUE" )
.queryParam( "dateTimeRenderOption", "FORMATTED_STRING" )
.queryParam( "majorDimension", "ROWS" )
.get( 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 get. status:${status}`;
}
const jsonObj = JSON.parse( responseStr );
if (jsonObj.values === undefined ) {
throw `No Data in range.`;
}
const dataStringList = jsonObj.values[0]
.slice(0, COLUMN_NUM) // 最大10列
.map( v => v.toString() ); // すべて String に変換
return dataStringList;
}
/**
* データ項目に出力する
* @param {Array<ProcessDataDefinitionView>} valueDefList 保存先データ項目の ProcessDataDefinitionView が格納された配列
* @param {Array<String>} dataStringList 出力するデータが格納された配列
*/
function setDataByLists( valueDefList, dataStringList ) {
valueDefList.forEach( (valueDef, i) => {
if ( valueDef !== null) {
if ( i > dataStringList.length - 1 ) { // dataStringList の要素がない場合
engine.setData( valueDef, null );
return;
}
const dataString = dataStringList[i];
// if ( valueDef.matchDataType("STRING") ) { // 保存先データ項目が文字型の場合
// 保存先データ項目が改行に対応しておらず、保存する文字列に改行が含まれる場合のエラーは QBPMS のバリデーションに任せる
engine.setData( valueDef, dataString );
// } else if ( valueDef.matchDataType("DECIMAL") ) { // 保存先データ項目が数値型の場合
// convertTypeAndSetData( valueDef, dataString, "DECIMAL", "Numeric" );
// } else if ( valueDef.matchDataType("SELECT") ) { // 保存先データ項目が選択型の場合
// convertTypeAndSetData( valueDef, dataString, "SELECT", "Select" );
// } else if ( valueDef.matchDataType("DATE") ) { // 保存先データ項目が日付型の場合
// convertTypeAndSetData( valueDef, dataString, "DATE", "Date" );
// } else if ( valueDef.matchDataType("DATETIME") ) { // 保存先データ項目が日時型の場合
// convertTypeAndSetData( valueDef, dataString, "DATETIME", "Datetime" );
// }
}
});
}
/**
* データ項目の型にしたがってデータを変換して出力する
* 対応しないフィールド型の場合はエラーとする
* 変換できない値の場合はエラーとする
* @param {ProcessDataDefinitionView} dataDef 保存先データ項目の ProcessDataDefinitionView
* @param {String} dataString 出力するデータ(変換前の文字列データ)
* @param {String} dataType 保存先データ項目のデータ型
* @param {String} dataTypeLabel 保存先データ項目のデータ型の表示名(エラー出力用)
*/
/*
function convertTypeAndSetData( dataDef, dataString, dataType, dataTypeLabel ) {
let convertedData;
if ( dataString === "" || dataString === null ) { // 空値の場合は null を設定
convertedData = null;
} else {
try { // try-catch でエラーを捕捉
switch (dataType) {
case 'DECIMAL':
convertedData = new java.math.BigDecimal( dataString );
break;
case 'SELECT': // 一致する選択肢 ID がない場合のエラーは QBPMS のバリデーションに任せる
convertedData = new java.util.ArrayList();
convertedData.add( dataString );
break;
case 'DATE':
convertedData = java.sql.Date.valueOf( dataString );
break;
case 'DATETIME':
const dateFormatter = new java.text.SimpleDateFormat( "yyyy-MM-dd HH:mm" );
convertedData = new java.sql.Timestamp( dateFormatter.parse( dataString ).getTime() );
break;
}
} catch (e) { // 変換できない値の場合はエラー
throw `Returned value "${dataString}" cannot be saved to ${dataTypeLabel} type data item.`;
}
}
engine.setData( dataDef, convertedData );
}
*/