How to get information on hidden rows from Google Sheet API without Google Apps Script

phlprcks

I am using the Google Sheets API V4 to retrieve its data in JSON like below. That includes all rows, even the ones currently not shown in the Spreadsheet UI because of a filtering in one of the columns.

Is there a way to get only the shown rows or information whether a row is hidden or not?

The Google Apps Script allows such data retrieval using i.e. the .hiddenByFilter Method (see https://cloud.google.com/blog/products/application-development/using-google-sheets-filters-in-add-ons ). However, I am not able to include that in my API Query whatsoever.

$.getJSON("https://sheets.googleapis.com/v4/spreadsheets/" + SpreadsheetID + "/values/Color1!A2:Q?key=<MY_API_KEY>", function(data) {

// data.values contains the array of rows from the spreadsheet. Each row is also an array of cell values.
    $(data.values).each(function() {
      var location = {};
      location.title = this[2];
      location.latitude = parseFloat(this[10]);
      location.longitude = parseFloat(this[9]);
      location.institution = this[3];
      //location.hidden = <?>;
      locations.push(location);
    });

Is there any workaround?

TheMaster

You can use Spreadsheets.get endpoint. Then filter sheets/data/rowData/values using sheets/data/rowMetadata/hiddenByUser field client side.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How to run a Google Apps Script on a hidden sheet

How do I get 5 rows from the end of my sheet in Google Sheets using Apps Script?

Using Apps Script, how can I display a google doc checklist from information in a google sheet?

How to get information from Google sheet using API

How to get Error Message from a Google sheet cell using Google Apps Script?

Apps Script: Fill template with rows of data from Google Sheet. HOW to make it more dynamic?

How to get all google sheet tab name from a public sheet without app script

How to get specific sheet by id in Google Apps script?

Google Apps Script - How to get email addresses from Sheet 2 and send email

How to get the specific range of row data from google sheet through apps script

Google Apps Script - How to send HTML email from Sheet?

get last update of google sheet in apps script

A more efficient way of transferring bulk amount of rows from sheet to sheet - Google Apps Script

Appending rows from a Google Sheet to an existing table in Big Query using Google Apps Script

How to Preserve Sheet Formats and Translate? ( Google Apps Script, google sheet )

Google Apps Script Copying certain cells from a sheet into another sheet without repeating data

How to get a Google Sheet script to skip empty rows?

How to Loop Through JSON URLs and get information from them inside Google Sheets using Apps Script

Google apps script - Importing sheet values from another sheet

Hyperlink the output result get from google sheet to Web app via Google Apps Script

Google Apps script : setValue unable to get results on google sheet

How to request or get the permission of google spread sheet access in the apps script of other sheet?

Duplicate some rows in a sheet range using google apps script

Google Sheet Apps Script Random Number in 5 Rows no repeat

How to Create an Array From JSON and Import the Values to Multiple Rows in a Google Sheet Using Google Apps Scripts

How to get the value from another sheet with rows using Google Sheets?

Wrap text using input from Google Sheet (Google Apps Script)

Need to Query Google Sheet for specific data from Google Apps Script

How can I fix the range of a Google Sheet MATCH function when using a move rows apps script?