Google Sheets: Two ValueRanges, Xlookup Reference
Google Sheets: Two ValueRanges, Xlookup Reference
Converts string B to string C by referring to the two spreadsheet areas. Searches lookup_string in range B, and if there is an exact match it returns the corresponding string in range C. If there is no match an empty string is returned. The search is executed in order from the top.
Configs
  • U: Select HTTP_Authz Setting *
  • A: Set FILE-ID in Drive *#{EL}
  • B: Set ValueRange to Search (e.g. “B:B”) *#{EL}
  • b: Set LookupString to Serch for (e.g. “JP”) *#{EL}
  • C: Set ValueRange to Return (e.g. “A:A”) *#{EL}
  • c: Select STRING that stores Returned String (update) *
Script (click to open)
// GraalJS Script (engine type: 2)

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

main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const strAuthzSetting     = configs.get      ( "AuthzConfU" );   /// REQUIRED
  engine.log( " AutomatedTask Config: Authz Setting: " + strAuthzSetting );
const strInputfileId      = configs.get      ( "StrConfA1" );    /// REQUIRED
  if( strInputfileId    === "" ){
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {A1: FileID} is empty \n" );
  }
const strSearchRange      = configs.get      ( "StrConfB1" );    /// REQUIRED
const strReturnRange      = configs.get      ( "StrConfC1" );    /// REQUIRED
const strSearch           = configs.get      ( "StrConfB2" );    /// REQUIRED
const strPocketReturn     = configs.getObject( "SelectConfC2" ); /// REQUIRED


//// == Data Retrieving / ワークフローデータの参照 ==
// (Nothing. Retrieved via Expression Language in Config Retrieving)


//// == Calculating / 演算 ==
/// Get Ranges
/// Sheets for Developers > API v4
/// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet
// request1, prepare
let request1Uri = "https://sheets.googleapis.com/v4/spreadsheets/" + strInputfileId + "/values:batchGet";
let request1    = httpClient.begin(); // HttpRequestWrapper
    request1    = request1.authSetting( strAuthzSetting ); // with "Authorization: Bearer XX"
    // https://questetra.zendesk.com/hc/en-us/articles/360024574471-R2300#HttpRequestWrapper
    request1    = request1.queryParam( "majorDimension", "ROWS" );
    request1    = request1.queryParam( "valueRenderOption", "FORMATTED_VALUE" ); 
                 // Even if formatted as currency, return "$1.23" not "1.23".
    request1    = request1.queryParam( "dateTimeRenderOption", "FORMATTED_STRING" ); 
                 // Date as strings (the spreadsheet locale) not SERIAL_NUMBER
    request1  = request1.queryParam( "ranges", strSearchRange );
    request1  = request1.queryParam( "ranges", strReturnRange );
// request1, try
const response1     = request1.get( request1Uri ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest1 Start: " + request1Uri );
const response1Code = response1.getStatusCode() + "";
const response1Body = response1.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + response1Code );
if( response1Code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response1Code + "\n" + response1Body + "\n" );
}
// response1, parse
/* 
engine.log( response1Body ); // debug
{
  "spreadsheetId": "1GOoZQmsExM7GoG6Uqa6R-_SujKVJu5SqrYLBqoQ378M",
  "valueRanges": [
    {
      "range": "'country-code'!B1:B1000",
      "majorDimension": "ROWS",
      "values": [
        [
          "AF"
        ],
        [
          "AX"
        ],
        [
          "AL"
        ],,,,
        [
          "ZW"
        ]
      ]
    },
    {
      "range": "'country-code'!A1:A1000",
      "majorDimension": "ROWS",
      "values": [
        [
          "Afghanistan"
        ],
        [
          "Aland Islands"
        ],
        [
          "Albania"
        ],,,,
        [
          "Zimbabwe"
        ]
      ]
    }
  ]
}
*/
const response1Obj = JSON.parse( response1Body );

/// Xlookup
// converts valueRange to Multiline String
let strMultilineSearch = "";
  for( let j = 0; j < response1Obj.valueRanges[0].values.length; j++ ){
    for( let k = 0; k < response1Obj.valueRanges[0].values[j].length; k++ ){
      strMultilineSearch += response1Obj.valueRanges[0].values[j][k];
      if( k != response1Obj.valueRanges[0].values[j].length - 1 ){
        strMultilineSearch += " ";
      }
    }
    if( j != response1Obj.valueRanges[0].values.length - 1 ){
      strMultilineSearch += "\n";
    }
  }
let strMultilineReturn = "";
  for( let j = 0; j < response1Obj.valueRanges[1].values.length; j++ ){
    for( let k = 0; k < response1Obj.valueRanges[1].values[j].length; k++ ){
      strMultilineReturn += response1Obj.valueRanges[1].values[j][k];
      if( k != response1Obj.valueRanges[1].values[j].length - 1 ){
        strMultilineReturn += " ";
      }
    }
    if( j != response1Obj.valueRanges[1].values.length - 1 ){
      strMultilineReturn += "\n";
    }
  }
// searches for a multiline string with a search term
let strReturn = "";
const arrSearch = strMultilineSearch.split("\n");
const arrReturn = strMultilineReturn.split("\n");
for( let i = 0; i < arrSearch.length; i++ ){
  if( strSearch === arrSearch[i] ){
    strReturn   = arrReturn[i];
    engine.log( " AutomatedTask Lookup: Match ID " + i );
    break;
  }
}
if( strReturn === "" ){
  engine.log( " AutomatedTask Lookup: No Match" );
}


//// == Data Updating / ワークフローデータへの代入 ==
engine.setData( strPocketReturn,    strReturn );


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


/*
Notes:
- Convert by referring to the master information (employee master, etc.) in the spreadsheet.
    - Example: Employee ID to Employee name / Employee name to Employee ID
    - Example: Country name to Country code / Country code to Country name
- The File ID can be obtained from the URI or the sharing settings screen.
    - docs.google.com/spreadsheets/d/{File-ID}/edit#gid=0
- The number of elements in the two ranges must be the same.
- How to specify the range depends on Google API A1 notation. (see APPENDIX)
- If multiple, each element will be string in which all cells are concatenated with spaces.
    - Example: Employee name data is stored separately in "{Firstname cell} {Lastname Cell}"
Notes-ja:
- スプレッドシートで管理しているマスター情報(社員マスタ等)を使って、データ変換できます。
    - 例:社員ID→社員の名前、社員の名前→社員ID、国名→国コード、国コード→国名
- ドキュメント ID は URL 等を参照します。
    - docs.google.com/spreadsheets/d/{File-ID}/edit#gid=0
- 2つの範囲の要素数は、同数でなければなりません。
- 範囲の指定方法は Google API の A1 notation に依ります (Appendix参照)
- 複数セルを指定した場合、「半角スペース結合された文字列」が探索対象/リターン対象となります。
    - A列に「姓」が、B列に「名」が、分けて保存されている場合、範囲指定は "A:B" と設定します。

APPENDIX-en
- A1 Notation
    - 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.
- Setting example of "HTTP Authentication" (OAuth2)
    - 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, Consumer Secret:
        - ( from https://console.developers.google.com/ )
        - Redirect URLs: https://s.questetra.net/oauth2callback
APPENDIX-ja
- A1記法(A1 Notation)
    - https://developers.google.com/sheets/api/guides/concepts#a1_notation
    - "Sheet1!A1:B2" 「Sheet1」の上2行の先頭2セル(合計4セル)を参照します。
    - "Sheet1!A:A" 「Sheet1」のA列の全てのセルを参照します。
    - "Sheet1!1:2" 「Sheet1」の上2行にあるすべてのセルを参照します。
    - "Sheet1!A5:A" 「Sheet1」のA列5行目以降のすべてのセルを参照します。
    - "A1:B2" 最初に表示されるシートの上2行の先頭2セル(合計4セル)を参照します。
    - "Sheet1" 「Sheet1」のすべてのセルを参照します。
- "HTTP認証"(OAuth2)の設定例
    - 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, Consumer Secret:
        - ( from https://console.developers.google.com/ )
        - Redirect URLs: https://s.questetra.net/oauth2callback
*/

Download

2021-05-10 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/google-sheets-two-valueranges-xlookup-reference-2021/
The Addon-import feature is available with Professional edition.

Notes

  • Convert by referring to the master information in the spreadsheet (employee master, etc.).
    • Example: Employee ID to Employee name / Employee name to Employee ID
    • Example: Country name to Country code / Country code to Country name
  • The File ID can be obtained from the URI or the sharing settings screen.
    • docs.google.com/spreadsheets/d/SPREADSHEETID}/edit#gid=0
  • The number of elements in the two ranges must be the same.
  • How to specify the range depends on Google API A1 notation. (see APPENDIX)
  • If multiple cells are specified, strings in which all cells are concatenated with spaces will be returned.
    • Example: Employee name data is stored separately in “{Firstname cell} {Lastname Cell}”

Capture

Converts stringB to stringC, referring to the two areas. Searches lookup_string in range B, and if an exact match, returns the corresponding string in range C. If no match, an empty string is returned. The search is executed in order from the top.
Converts stringB to stringC, referring to the two areas. Searches lookup_string in range B, and if an exact match, returns the corresponding string in range C. If no match, an empty string is returned. The search is executed in order from the top.

Appendix

See also

1 thought on “Google Sheets: Two ValueRanges, Xlookup Reference”

  1. Pingback: Google Sheets: Master Table, Simple Xlookup – Questetra Support

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d