CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsmetadata
Ivan
Ivan

Reputation: 6013

How to efficiently add developer metadata to sheets' row/column ranges?

I am trying to add developer metadata to certain row ranges with Google Apps Script. So far I cobbled together this piece of code:

function updateMetadata() {
  var sheet = SpreadsheetApp.openById("<sheet id>");
  var dataRange = sheet.getDataRange();
  var productNameRanges = dataRange.getMergedRanges();
  productNameRanges.forEach(function(productRange){
    var productName = productRange.getCell(1, 1).getValue();
    for (var j=productRange.getRow(); j<=productRange.getLastRow(); j++){
      var range = sheet.getRange(j+":"+j);
      // Remove old metadata
      range.getDeveloperMetadata().forEach(function(md){
        md.remove();
      });
      // Add new metadata
      range.addDeveloperMetadata(
        "product_name", 
        productName, 
        SpreadsheetApp.DeveloperMetadataVisibility.PROJECT
      );
    }
  });
}

The purpose of this was supposed to be to mark merged cell ranges with a metadata value to make it possible to retrieve those ranges by the merged cells' value. So far though, the function never terminates, the last execution duration that is shown is around 8 seconds. The metadata still gets modified though, but I never checked if it was for all the ranges.

Is there any other (efficient, or right at all) way to achieve this with apps script? Can I maybe somehow mark row ranges with a single method call instead of iterating over rows (it is not possible according to error messages I get when I am trying to)?

Upvotes: 1

Views: 986

Answers (1)

TheMaster
TheMaster

Reputation: 50443

Upvotes: 2

Related Questions