Google スプレッドシート: 指定値で行番号を特定

  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 + "");