Google Sheets: Get Row
Gets data in a specified row from a Google Sheet.
Configs: Common
  • Step Name
  • Note
Configs
  • C1: OAuth2 Setting *
  • C2: Source Spreadsheet ID *
  • C3: Source Sheet Title *
  • C4: Row to Get (e.g. “1”, “11”) *
  • C5: Column Range to Get (e.g. “A:J”)(Up to 10 columns) *
  • C6_1: Data item that stores the value in the 1st column
  • C6_2: Data item that stores the value in the 2nd column
  • C6_3: Data item that stores the value in the 3rd column
  • C6_4: Data item that stores the value in the 4th column
  • C6_5: Data item that stores the value in the 5th column
  • C6_6: Data item that stores the value in the 6th column
  • C6_7: Data item that stores the value in the 7th column
  • C6_8: Data item that stores the value in the 8th column
  • C6_9: Data item that stores the value in the 9th column
  • C6_10: Data item that stores the value in the 10th column

Notes

  • Spreadsheet ID is contained in the URL. https://docs.google.com/spreadsheets/d/(Sheet ID)/edit#gid=0

Capture

Appendix

  • google-sheets-row-get.xml (C) Questetra, Inc. (MIT License)
    • If you are using Professional, you can modify the contents of this file and use it as your own add-on

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.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 bloggers like this: