3

I have a Spreadsheet script that creates other Spreadsheet documents. I would like to set custom properties on these objects, ideally using the Properties object.

Currently:

var props = PropertiesService.getDocumentProperties()

Will only return the Properties object associated with the currently active document. Is there a way to access the Properties object of external documents?

The only work-around I have at the moment is to overload a header cell by adding a note to it:

dataRange.setNote("foo=bar")
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
Dave
  • 1,196
  • 15
  • 22

2 Answers2

2

Is there a way to access the Properties object of external documents?

No. Properties cannot be shared between scripts.ref A script can only modify its own properties (be they ScriptProperties, UserProperties or DocumentProperties).

However, you can expose a scripts Properties via functions - for example, a library could provide an API to multiple scripts so that they could read & write a set of common properties in the library. See How to pass parameter(s) to timed trigger function in library script for more about that.

You could adapt that to your situation by using the library's properties as a communication between your master script and the slave spreadsheet scripts, or you could use slave sheet IDs as keys to properties for slave sheets that are stored in the library's properties.

The question you've posed is an example of an XY Problem, so it's possible that your focus on PropertiesService is precluding solving your actual problem. If that's the case, you may wish to ask another question that focuses on the problem, rather than a possible solution.

Community
  • 1
  • 1
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • I think it's not a correct answer, because OP didn't mention other script, he mentioned accessing properties from same script, but for other spreadsheets than active, and it turns out impossible. – Kos Nov 02 '21 at 12:37
0

Correct answer is: not possible.

Explanation (from getDocumentProperties, bold is mine):

Gets a property store (for this script only) that all users can access within the open document, spreadsheet, or form. It is only available if the script is published and executing as an add-on or if it is bound to a Google file type. When document properties are not available this method returns null. Document properties created by a script are not accessible outside that script, even by other scripts accessing the same document.

So document properties not accessible from other scripts, that's OK, but that's not an only restriction: they are also accessible ONLY at a time when you are actually using document where they are stored.

TLDR: you can get document properties only of active document.

Kos
  • 4,890
  • 9
  • 38
  • 42