CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
CircularRecursion
CircularRecursion

Reputation: 460

Get specific developer metadata references

Adding metadata to a sheet in Google Sheets is really easy. Take for example:

  var newSheet = ss.insertSheet(obj.jobName); 
  newSheet.addDeveloperMetadata(metaKey, metaValue);

I now want to retrieve said data. As far as I can see, there isn't an easy way to just retrieve a value by key? I can see the DeveloperMetadataFinder class, which I'm still trying to wrap my head around. I assume that's what I need to be using? But surely there must be a simpler way?

I know I can get all of the metadata using the following code:

  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (i = 1; i < sheets.length; i++) { 
    sheets[i].getDeveloperMetadata();
  }

However, that code returns all of the metadata, and then you cannot seem to access the data by the metaKey that has been used.

Upvotes: 1

Views: 1763

Answers (2)

Alan Wells
Alan Wells

Reputation: 31300

Is there an easy and simple way to make 100% sure that the metadata that you saved is the metadata that you retrieved? No. There is a way, but it's not simple and easy.

Metadata keys don't get replaced. It is possible to have multiple metadata objects with exactly the same key name. They will however have different ID numbers. You can prove this to yourself with the code below.

The code below adds metadata 3 times with the same key name and value. Then it gets all the metadata, loops through the object, logging the name, id and value. Finally it deletes the metadata object.

function testMetaData() {
  var ss = SpreadsheetApp.getActive();

  for (var i = 0;i<3;i++) {//Loop 3 times
    ss.addDeveloperMetadata('keyOne','valueOne');//Add metadata with the same key name and value
  }

  var mtaData = ss.getDeveloperMetadata();
  Logger.log(mtaData.length)//The number of meta data objects in the spreadsheet

  mtaData.forEach(//Loop through every element of the meta data
    function(thisMetaData) {
      Logger.log(thisMetaData.getValue())
      Logger.log(thisMetaData.getId())
      Logger.log(thisMetaData.getKey())
      thisMetaData.remove();//Remove this metadata
    }
  )
}

Because there can be multiple metadata objects with the same key name, you need to be very careful how you set and get metadata. If there is no metadata set, then you could add metadata, then get the metadata, then get the first and only element, and then get the ID of that element. Then store the ID in Properties Service. The only way that you can be sure that the metadata that you've retrieved is the metadata that you expected, is to check the ID number.

If there is metadata that has already been added, and there are multiple metadata objects with the same key name, and you don't know the ID that you need, then you may want to delete those metadata objects with the same key name, and start over.

Once you have the ID of the metadata, then you can find that particular metadata by ID, and there won't be any duplicates. Obviously, you'll need to know the ID, but the code would need to know the key name if you were using a key name. So, either way you need to have the key or ID available somehow.

function getMetaDataByID() {
  var finder,
      id,
      mtaData,
      numberOfMetaDataObjectsWithTheSameKeyName = 0,
      ss;

  ss = SpreadsheetApp.getActive();
  addDeveloperMetadata('myKeyName','valueOne');//

  mtaData = ss.getDeveloperMetadata();
  Logger.log(mtaData.length)//The number of meta data objects in the spreadsheet

  mtaData.forEach(//Loop through every element of the meta data
    function(thisMetaData) {
      var thisKey;

      thisKey = thisMetaData.getKey();

      Logger.log(thisMetaData.getValue())
      Logger.log(thisMetaData.getId())
      Logger.log(thisKey)

      if (thisKey === 'myKeyName') {
        numberOfMetaDataObjectsWithTheSameKeyName++;
        PropertiesService.getDocumentProperties().setProperty('id_Of_My_MetaData', thisMetaData.getId());
      }
    }
  )

  Logger.log('numberOfMetaDataObjectsWithTheSameKeyName: ' + numberOfMetaDataObjectsWithTheSameKeyName)

  //Now get the metadata by ID
  id = PropertiesService.getDocumentProperties().getProperty('id_Of_My_MetaData');
  Logger.log('id: ' +   id);
  finder = ss.createDeveloperMetadataFinder()
           .withId(id)
           .find()[0];

  var metaDataValue = finder.getValue();

  Logger.log('metaDataValue: ' + metaDataValue)
}

Upvotes: 2

TheMaster
TheMaster

Reputation: 50443

You can use DeveloperMetadataFinder like this:

const devMetaData = SpreadsheetApp.getActive()
    .createDeveloperMetadataFinder()//@see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#createDeveloperMetadataFinder()
    .withKey(/*METADATA_KEY*/)
    .find()[0];

Upvotes: 2

Related Questions