CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Kajsa
Kajsa

Reputation: 419

Get all of the developer metadata in a Google spreadsheet

I have a Google spreadsheet with a number of sheets and some metadata associated with certain rows and columns in the different sheets. I would like to have a script collect ALL the metadata in the whole file so I can check the state of it.

I can get the metadata for a row or column easily enough with:

function showMetadata(range){
  var metadata = range.getDeveloperMetadata();
  var msg = metadata[0].getLocation().getLocationType();
  for (var i = 0; i < metadata.length; i++){
    msg = msg+"\\n"+metadata[i].getKey()+": "+metadata[i].getValue()
  }
  Browser.msgBox(msg)
}

Is there a way of getting all the metadata without having to iterate through each sheet, row and column and calling .getDeveloperMetadata() on each of them?

Upvotes: 0

Views: 917

Answers (1)

chuckx
chuckx

Reputation: 6877

Use the find() method of the DeveloperMetadataFinder class.

Here's a simple example:

function addMetadata() {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spreadsheet.getActiveSheet();
    var range = sheet.getRange('1:1');
    sheet.addDeveloperMetadata('sheet');
    spreadsheet.addDeveloperMetadata('spreadsheet');
    range.addDeveloperMetadata('range');
}

function getMetadata() {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var metadataFinder = spreadsheet.createDeveloperMetadataFinder();
    results = metadataFinder.find();
    for (var i = 0; i < results.length; i++) {
       Logger.log('id: ' + results[i].getId() + ', key: ' + results[i].getKey());
    }
}

After running addMetadata() to populate a new spreadsheet with 3 instances of metadata, here's the logging output from running getMetadata():

[19-11-12 08:34:54:874 PST] id: 375645033, key: spreadsheet
[19-11-12 08:34:54:875 PST] id: 802864327, key: range
[19-11-12 08:34:54:875 PST] id: 569198748, key: sheet

Upvotes: 2

Related Questions