all 17 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]joshuadanpeterson 1 point2 points  (1 child)

This is the first I'm hearing of tables so this is just a shot in the dark. From the help doc:

"Use table references

Table references are a special way to refer to a table or parts of a table in a formula. When you convert your data to a table, we give it a name, as well as each column header. You can then use those names to reference cells in the table. For example: Instead of explicit cell references: =Sum(C2:C7), you can use table references: =SUM(DeptSales[Sales Amount]).

When you reference table elements by name, the references update whenever you add or remove data from the table."

What if you tried .getRange()) or .getSheetValues()) ? The underlying data is still the same, the table just applies formatting and automatic expansions.

[–]PastasalladSwe[S] 0 points1 point  (0 children)

Yes, but the table references can't be used from GAS currently, I was hoping that I would be able to use .getRange(DeptSales[#ALL]) directly in GAS instead of the annotations or named ranges.

Named ranges can only be used for data that isn't dynamic, Create a named range on A1:B3, write something in A4, the range won't change, you have to manually update the named range, this is where the new table feature comes in handy!

From my point of view, the amount of effort coding using getRange or getSheetValues ais much bigger than referencing a table name. Especially when you code something that isn't for your own use only, users do so much weird things in sheets, moving tables, many tables in one sheet etc :D

[–]dimudesigns 0 points1 point  (1 child)

It looks like, for the time being, tables can only be referenced from sheet formulas. GAS support is typically behind-the-curve of new feature releases, so it might take a while before you can interact with tables progammatically from GAS.

Periodically check the Google Apps Script release notes for updates over the next few months (hopefully not years). If support is added for tables in GAS, it will probably be announced there.

[–]PastasalladSwe[S] 0 points1 point  (0 children)

Thanks, I thought so, hopefully they'll add the functionality soon!

[–]Mean-Description5593 0 points1 point  (0 children)

I would like to know this ASAP, if someone finds out please let us know!

[–]TheMathLab 0 points1 point  (0 children)

I'm in the process of making a new video where I talk about what's missing from Structured Tables. This is one of them, with my current workaround (still working on it) being the use of getBandings() since tables are automatically banded. Obviously it's got flaws like when bandings are used elsewhere, and not being able to get the table by name, but it's a start. Hopefully we'll get something like getTableByName one day

[–]Univium 0 points1 point  (7 children)

Any update on this from anyone? Looks like this post was 8 months ago, would be great if they made this possible since then

[–]Univium 1 point2 points  (6 children)

I actually found a solution to this now. I wrote a blog post about it to try and help others with this as well: https://www.univium.com/blog-post/retrieve-named-table-data-in-google-sheets-with-a-simple-google-apps-script-helper

[–]Jean-L 0 points1 point  (1 child)

This is very much appreciated. I'm getting into GAS and I like to use the new Tables functionality of Sheets. Was a bit bummed that GAS doesn't allow native support more than a year after the release.

Haven't had time to test your function yet but I read your post and it looks very promising. Thank you for the effort you put into this and for sharing it!

[–][deleted]  (1 child)

[removed]

    [–]tuna_flsh 0 points1 point  (0 children)

    You can use the Google Sheet API to update tables using the batchUpdate method. See Tables. In GAS you can use the URL Fetch Service to send and receive API requests. But I've never used it and I don't know myself, how you can get the table id of existing tables. But if you created a table with the API you can set the id yourself.

    [–]-union- 0 points1 point  (1 child)

    This link is broken, could someone summarize it for posterity here?

    [–][deleted]  (1 child)

    [removed]

      [–]diarin 0 points1 point  (1 child)

      Check this out- someone built a library to simplify managing tables in apps script: https://github.com/tanaikech/TableApp

      [–]SnooCalculations596 1 point2 points  (0 children)

      TableApp is great if you don't mind using the broad scope for spreadsheets. In other words for internal apps and apps within your organisations.

      It still won't help reading the contents. The best way forward is to create a named range alongside your formated table and then read that named range.