Google スプレッドシート: マスターテーブル, シンプル Xlookup

Google スプレッドシート: マスターテーブル, シンプル Xlookup
Google スプレッドシート: マスターテーブル, シンプル Xlookup (Google Sheets: Master Table, Simple Xlookup)

Google スプレッドシート内のマスタデータを参照して値を返します。配列Cの中に検索語を検索し、完全一致する要素があれば、それに対応する配列D内の値(同じインデックスの値)を返します。探索配列Cとリターン配列Dは、A1記法で定義します。配列Cと配列Dの長さが同数でない場合はエラーとなります。

2020-02-06 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/ja/addons/google-sheets-master-table-simple-xlookup/

Configs
  • A: 通信許可の設定名([OAuth2.0設定]メニュー)を選択してください *
  • B: Spreadsheetファイルの Document-ID をセットしてください (ファイルURI内の44文字) * #{EL}
  • C: 探索配列のデータ範囲をセットしてください (例 “Sheet1!C:C”) * #{EL}
  • D: リターン配列のデータ範囲をセットしてください (例 “Sheet1!D:D”) * #{EL}
  • E: 検索語をセットしてください (例 “JP”) * #{EL}
  • F: 戻り値が格納される文字列型データを選択してください (更新) *
Script
// (c) 2020, Questetra, Inc. (the MIT License)

//// == OAuth2 Setting example ==
// 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"
// Client ID:
//  ( from https://console.developers.google.com/ )
// Consumer Secret:
//  ( from https://console.developers.google.com/ )
//  *Redirect URL of Webapp OAuth-Client-ID: "https://s.questetra.net/oauth2callback"

//// https://developers.google.com/sheets/api/guides/concepts#a1_notation
// Sheet1!A1:B2 refers to the first two cells in the top two rows of Sheet1.
// Sheet1!A:A refers to all the cells in the first column of Sheet1.
// Sheet1!1:2 refers to the all the cells in the first two rows of Sheet1.
// Sheet1!A5:A refers to all the cells of the first column of Sheet 1, from row 5 onward.
// A1:B2 refers to the first two cells in the top two rows of the first visible sheet.
// Sheet1 refers to all the cells in Sheet1.

// lookup_value,lookup_array,return_array

// Notes:
// C and D length must be the same. 
// The search is only performed vertically.
// The search is performed in order from the top and ends when a exact match.
// No problem if return array D is on the left side of search array C.
// 
// Notes(ja):
// 配列Cと配列Dの要素数は同数でなければなりません。
// 探索は縦方向にのみ実行されます。
// 探索は上から順に実行され完全一致した時点で終了します。
// リターン配列Dは探索配列Cの左側にあっても問題ありません。

//////// START "main()" /////////////////////////////////////////////////////////////////

main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const oauth2      = configs.get( "conf_OAuth2"  )     + ""; // required
const documentId  = configs.get( "conf_DocumentId" )  + ""; // required
const valueRangeC = configs.get( "conf_ValueRangeC" ) + ""; // required
const valueRangeD = configs.get( "conf_ValueRangeD" ) + ""; // required
const lookupValue = configs.get( "conf_LookupValue" ) + ""; // required
const dataIdF     = configs.get( "conf_DataIdF" )     + ""; // required

engine.log( " AutomatedTask Config: Document ID: "  + documentId );
engine.log( " AutomatedTask Config: LookupArrayC: " + valueRangeC );
engine.log( " AutomatedTask Config: ReturnArrayD: " + valueRangeD );
if( lookupValue === "" ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {LookupValue E} is empty \n" );
}


//// == Data Retrieving / ワークフローデータの参照 ==
// (nothing)


//// == Calculating / 演算 ==
/// obtain OAuth2 Access Token
const token   = httpClient.getOAuth2Token( oauth2 );

/// batch get two ranges of values via Google Sheets API v4
// https://developers.google.com/sheets/api/reference/rest
//  /v4/spreadsheets.values/batchGet
let apiRequest = httpClient.begin(); // HttpRequestWrapper
    apiRequest = apiRequest.bearer( token );
    apiRequest = apiRequest.queryParam( "ranges", valueRangeC ); 
    apiRequest = apiRequest.queryParam( "ranges", valueRangeD ); 
    apiRequest = apiRequest.queryParam( "majorDimension", "ROWS" ); 
    apiRequest = apiRequest.queryParam( "valueRenderOption", "FORMATTED_VALUE" ); 
                 // If formatted as currency, return "$1.23" not "1.23".
    apiRequest = apiRequest.queryParam( "dateTimeRenderOption", "FORMATTED_STRING" ); 
                 // Date as strings (the spreadsheet locale) not SERIAL_NUMBER
const apiUri = "https://sheets.googleapis.com/v4/spreadsheets/" +
                documentId + "/values:batchGet";
engine.log( " AutomatedTask Trying: GET " + apiUri );
const response = apiRequest.get( apiUri );
const responseCode = response.getStatusCode() + "";
engine.log( " AutomatedTask ApiResponse: Status " + responseCode );
if( responseCode !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
         responseCode + "\n" + response.getResponseAsString() + "\n" );
}
const responseStr = response.getResponseAsString() + "";
const responseObj = JSON.parse( responseStr );
// engine.log( responseStr );

let strLookupArray = "";
  for( let j = 0; j < responseObj.valueRanges[0].values.length; j++ ){
    for( let k = 0; k < responseObj.valueRanges[0].values[j].length; k++ ){
      strLookupArray += responseObj.valueRanges[0].values[j][k];
      if( k != responseObj.valueRanges[0].values[j].length - 1 ){
        strLookupArray += " ";
      }
    }
    if( j != responseObj.valueRanges[0].values.length - 1 ){
      strLookupArray += "\n";
    }
  }
//engine.log( " AutomatedTask Response of ValueRangeC: \n " + strLookupArray );

let strReturnArray = "";
  for( let j = 0; j < responseObj.valueRanges[1].values.length; j++ ){
    for( let k = 0; k < responseObj.valueRanges[1].values[j].length; k++ ){
      strReturnArray += responseObj.valueRanges[1].values[j][k];
      if( k != responseObj.valueRanges[1].values[j].length - 1 ){
        strReturnArray += " ";
      }
    }
    if( j != responseObj.valueRanges[1].values.length - 1 ){
      strReturnArray += "\n";
    }
  }
//engine.log( " AutomatedTask Response of ValueRangeD: \n " + strReturnArray );

/// search value
let returnValue = "";
const arrLookup = strLookupArray.split("\n");
const arrReturn = strReturnArray.split("\n");
for( let i = 0; i < arrLookup.length; i++ ){
  if( lookupValue === arrLookup[i] ){
    returnValue = arrReturn[i];
    engine.log( " AutomatedTask Lookup: Match ID " + i );
    break;
  }
}
if( returnValue === "" ){
  engine.log( " AutomatedTask Lookup: No Match" );
}

//// == Data Updating / ワークフローデータへの代入 ==
engine.setDataByNumber( dataIdF, returnValue );

} //////// END "main()" /////////////////////////////////////////////////////////////////

Download

Capture

Google スプレッドシート内のマスタデータを参照して値を返します。配列Cの中に検索語を検索し、完全一致する要素があれば、それに対応する配列D内の値(同じインデックスの値)を返します。探索配列Cとリターン配列Dは、A1記法で定義します。配列Cと配列Dの長さが同数でない場合はエラーとなります。

Notes

  1. 配列Cと配列Dの要素数は同数でなければなりません。
  2. 探索は縦方向にのみ実行されます。
  3. 探索は上から順に実行され完全一致した時点で終了します。
  4. リターン配列Dは探索配列Cの左側にあっても問題ありません。
  5. ドキュメント ID は、URL に含まれています。https://docs.google.com/spreadsheets/d/(sheet ID)/edit#gid=0
  6. 領域指定されたとしても、データの無い列や行については無視されます。
  7. 範囲の指定方法は Google API の A1 notation に依ります。
    1. “Sheet1!A1:B2” は、シート名 “Sheet1” の、上から2行の先頭から2セルを指します
    2. “Sheet1!A:A” は、シート名 “Sheet1” の、先頭列(A列)の全てのセルを指します
    3. “Sheet1!1:2” は、シート名 “Sheet1” の、上から2行の全てのセルを指します
    4. “Sheet1!A5:A” は、シート名 “Sheet1” の、先頭列(A列)の5行目(A5セル)以降すべてのセルを指します
    5. “A1:B2” は、先頭に表示されているシートの、上から2行のそれぞれ先頭から2セル(2列)を指します
    6. “Sheet1” は、シート名 “Sheet1” の、全てのセルを指します
  8. 配列定義に複数列範囲を指定した場合(例 “Sheet1!A:B”)、行の値(配列要素の値)は半角スペース結合した文字列となります。(”{姓セル} {名セル}”)

See also

コメントを残す

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

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