2022-11-15 / Add-ons /
Google Sheets: Find Row Number By Specified Value
Finds the row number that contains a specified value in the target column of a Google Sheet.
Configs
  • C1: OAuth2 Setting *
  • C2: Source Spreadsheet ID *
  • C3: Source Sheet Title *
  • C4: Target Col (e.g. “A”) *
  • C5: Start Row of Target Col (e.g. “D2:D”) *
  • C6: String type data item for specified value *
  • C7: String type data item to save row number *
Script (click to open)


  const oauth = configs.get( "conf_OAuth2" );
  const docId = configs.get( "conf_DataIdB" );
  const sheet = configs.get( "conf_DataIdC" );
  const col = configs.get( "conf_DataIdD" );
  const startRow = configs.get( "conf_DataIdE" );

  const key = engine.findDataByNumber( configs.get("conf_DataIdF") ) + "";
  const valDataDef = configs.get( "conf_DataIdG" );

  const range = col + startRow + ":" + col;

  const uri = `https://sheets.googleapis.com/v4/spreadsheets/${docId}/values/:batchGet`;
  const response = httpClient.begin()
    .authSetting( oauth )
    .queryParam( "ranges", `${sheet}!${range}` )
    .queryParam( "valueRenderOption", "UNFORMATTED_VALUE" )
    .queryParam( "majorDimension", "COLUMNS" )
    .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 );

  const keyList = jsonObj.valueRanges[0].values[0];

  let row = -1;
  for (let i = 0; i < keyList.length; i++) {
    if (keyList[i] == key) {
      row = parseInt(startRow) + i;
      break;
    }
  }

  //// == ワークフローデータへの代入 / Data Updating ==
  engine.setDataByNumber( valDataDef, row + "");

Download

2021-10-10 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/google-sheets-find-row/
The Add-on import feature is available with Professional edition.
Freely modifiable JavaScript (ECMAScript) code. No warranty of any kind.

Notes

  • Thinking of Google Sheets as a master data management database, we will search a specified column with a specified value and identify the row number. Using the identified row number, it is assumed that the data will be retrieved by [Google Spreadsheet: Get Row].
  • Returns -1 if there are no applicable rows.

Capture

Appendix

%d bloggers like this: