2

I need to associate information with a row in a Google Sheet programmatically, with minimal user interaction. The association must stay associated with the same row, even when a row is added or deleted above. Also, the information should be deleted if the row is.

To me, this sounds like a job for DeveloperMetadata.

I would like to do this without the API for now - to avoid adding more variables. I would use named ranges before I would use the API.

Anyways, the Spreadsheet, Sheet, and Range classes all have the method: addDeveloperMetadata, with various inputs - none of which have a DeveloperMetadataLocationType input (problem #1).

Interestingly, as suggested by the answer here, the following code DOES add developer metadata to a column:

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("H:H").addDeveloperMetadata("columnName", "price");

However, I can not for the life of me figure out how to create a "ROW" range to add metadata. I have tried the following:

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1, 1, 1, sheet.getMaxColumns()).addDeveloperMetadata("rowStatus", "processing");

and

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("A2:L2").addDeveloperMetadata("rowStatus", "processing");

where L is the last column.

In both cases, I get the following error:

Adding developer metadata to arbitrary ranges is not currently supported. Developer metadata may only be added to the top-level spreadsheet, an individual sheet, or an entire row or column.

Is there any way to indicate that a range is a ROW, so I can add metadata to it?

Matthew Beck
  • 451
  • 5
  • 19
  • This is interesting. Why did you want the information hidden rather than stored in a note or cell? – J. G. Jan 08 '19 at 21:38
  • The cell values, notes and comments are all important for other reasons - I need the metadata for something super programm-y that I don't want distracting the user. – Matthew Beck Jan 09 '19 at 00:32

1 Answers1

3

And as soon as I post the question, I figure out the answer.

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("2:2").addDeveloperMetadata("rowStatus", "processing");

Found the answer by pretending to create a Conditional Format Rule, and selected the row number when indicating the range to see how Google Sheets A1 notation represents rows.

Matthew Beck
  • 451
  • 5
  • 19