i have a google sheet that has multiple pages inside, and we wish to use hypertext. but default n8n nodes do not support it and return just raw hyperlink text without the url
so then i thought well, lets do custom api call. and for my surprise, google api is to say the least “bad”
we barely have any data inside, basically it fits on 4 A4 pages on paper. the hell is that
i have tried multiple tricks, even hacks, i already run custom n8n server but further i dive more unsupported stuff there are. and i undestand why, i cannot judge n8n, it is the greatest tool in existance.
Hi @Anton_Varabei
when you pass includeGridData=true without specifying a range, google doesn’t just return your 4 pages of actual data. it returns the background colors, font sizes, and metadata for every single empty cell in the entire workbook — usually all the way down to row 1000 on every tab. that’s exactly why your node is choking on a 131mb payload for such a tiny sheet.
you can keep your custom http request, you just need to put blinders on it.
just add another query parameter in that same node called ranges and set it to the specific tab and columns you actually care about, like Sheet1!A1:Z100. this stops it from pulling the thousands of empty rows.
Yeah the trick is you need to limit the range in the API URL itself, something like https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}?ranges=SheetName!A1:E50&includeGridData=true where SheetName is the actual tab name (not the ID). That way google only sends back the grid data for that specific range instead of dumping every cell in the entire spreadsheet. The hyperlink info lives in sheets[0].data[0].rowData[x].values[y].hyperlink in the response, you’ll need a Code node to pull that out and map it back to your rows since the structure is pretty nested.
actually this is better i think, but i resorted to app scripting the sheet itself to de-hyperlink the sheet just added more columns that contains direct links. shitty =yes, works=yes, scalable=yes
then proper future solution still awaits. that’s the issue with google sheet.
just added more columns that contains direct links. shitty =yes, works=yes, scalable=yes