Google スプレッドシート: 行取得

Google Sheets: Get 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”) (最大 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 );
  }
 */

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