Get row number from a Google Visualization API query to a Google Sheet

Question

I have a query to a google sheet that is working as I expect. I'm using Postman to do my testing because in my app — which is a desktop app, I cannot use any of the SDKs that Google provides. This is not a web app, so I cannot use even Javascript source that is hosted on Google's CDN. In any case, I'm querying a spreadsheet that has many rows, with a particular column that I'm querying for:

I'm using the following endpoint, after having retrieved an authentication token via OAuth2 methodology:

https://docs.google.com/a/google.com/spreadsheets/d/{{sheet_key}}/gviz/tq?sheet={{tab_name}}&tq={{sheet_query}}

The {{sheet_query}} variable in Postman, which handles the URL encoding properly is set to the following:

select * where C = 'ready'

The response for my particular worksheet is what I would expect and the data is correct. There are three rows in the sheet where column C is set to 'ready' which is great.

Now, the response to this GET request does not appear to send back the ordinal row index for the retrieved rows, it just send the rows' data. The problem is that I want to write back to these rows via the Google Sheets API v4 which now uses the A1 notation as a method to target which cell range to update.

The problem is that in order to write back to these rows retrieved via my Visualization API request, I would need the rows' ordinal indices for the requests to the Google Sheets API. Is there any way to get each row's ordinal index from the Sheet in the request to the Visualization API call?

I found this Stack Overflow thread, but I'm not exactly sure if this can work in my case as I'm essentially building the requests manually and without an SDK. I'm also not using Google Apps Script, I'm using the Visualization API. The only workaround I can think of is to force the end-user to ensure the worksheet to be queried has a column whose cells have the =ROW() formula, but I would rather not make that a requirement on the user's part.

Any insights or guidance is appreciated.

Answer 1

• You want to retrieve the row numbers by searching a value from the sheet in Google Spreadsheet.
• You want to achieve this using Google Apps Script.

I could understand like above. If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Flow:

1. Retrieve the sheet object.
2. Search values in a sheet using a search value.
   • In this case, I used TextFinder.
3. Retrieve the row numbers from the searched result.

Sample script:

Please copy and paste the following sample script to the script editor, and set the variables of searchValue, spreadsheetId, sheetName. Then, as a test run, please run the function of testRun(). By this, the row numbers are returned as an array.

function getRowNumberBySearch(searchValue, spreadsheetId, sheetName) {
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  var t = sheet.createTextFinder(searchValue).findAll();
  return t.map(function(e) {return e.getRow()});
}

// Please run this function.
function testRun() {
  var searchValue = "sample";
  var spreadsheetId = "###";
  var sheetName = "Sheet1";

  var res = getRowNumberBySearch(searchValue, spreadsheetId, sheetName);
  Logger.log(res)
}

References:

• Class TextFinder
• map()

Added:

Additional question 1:

is there a way to narrow the .findAll() method to a specific range within the sheet so that it focuses on a single column?

Sample script:

function getRowNumberBySearch(searchValue, spreadsheetId, sheetName, range) {
  var range = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName).getRange(range);
  var t = range.createTextFinder(searchValue).findAll();
  return t.map(function(e) {return e.getRow()});
}

• In this case, range is a1Notation.

Additional question 2:

is it possible to return all of the values of the row and not just their index?

Sample script:

function getRowNumberBySearch(searchValue, spreadsheetId, sheetName, range) {
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  var t = sheet.getRange(range).createTextFinder(searchValue).findAll();
  return t.map(function(e) {return sheet.getRange(e.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0]});
}

• In this case, range is a1Notation.
• I think that in this case, the endpoint of your question can be also used.