google sheetsworksheet-function
I'm using the ArrayFormula to calculate multiple ranges and it's slow because it's trying to calculate everything in that range, even though it has nothing in it.
Is there a way I can limit the ArrayFormula to only calculate a certain amount of rows in a range?
I was able to make 4963 named ranges, before the execution time of the Google Apps Script expired, using this piece of code.
function namedRanges() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
for(var i=1; i<5000; i++) {
var r = ss.getRange('A'+i);
ss.setNamedRange("test"+i, r);
}
}
If you plan on making larger named ranges (I created only one celled ranges), I guess it will take longer and thus reduce the amounts you can make.
If you're about to add them manually, I'd say you're safe.
It's not possible to achieve the desired result by using IMPORTXML alone, I think. Instead use Google Apps Script and the XML Service Service.
Example:
function parseFeedXml(url) {
var result = [];
var xml = UrlFetchApp.fetch(url).getContentText();
var document = XmlService.parse(xml);
var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
var feed = document.getRootElement();
var entry = feed.getChildren('entry',atom);
for (var i = 0; i < entry.length; i++) {
var content = entry[i].getChildren('content',atom);
var contentElement = '';
var resultRow = [];
for (var j = 0; j < content.length; j++) {
contentElement = content[j];
evaluateText(contentElement.getChild('filing-date',atom), resultRow);
evaluateText(contentElement.getChild('filing-type',atom), resultRow);
evaluateText(contentElement.getChild('filing-href',atom), resultRow);
evaluateText(contentElement.getChild('form-name',atom), resultRow);
evaluateText(contentElement.getChild('size',atom), resultRow);
evaluateText(contentElement.getChild('amend',atom), resultRow);
result.push(resultRow);
}
}
return result;
}
function evaluateText(element, resultRow) {
if (element) {
resultRow.push(element.getText());
} else {
resultRow.push('');
}
}
Best Answer
If your are using as a parameter a range that is too large, i.e. whole columns references like A:A, but you don't require the whole range, change it to a smaller range like A1:10. Also you could use functions like FILTER, QUERY, OFFSET, among others, to return small enough ranges.