0

How to find out how many importranges are being used in a single Google Sheet?

0

The following script is heavily adapted from an Audit Spreadsheet built by Ben Collins.

The logic followed is:

  • get the spreadsheet by url openByUrl(); though the script could just as easily have been bound to the 'IMPORTRANGE' spreadsheet
  • get all the sheets getSheets()and loop through each them
  • report on results using a Logger statement

function wa13548203() {

  /** 
* Adapted by Ted Bell for Webapps StackExchange#135482
* from   
* Google Sheets Performance Auditor tool
* Built by Ben Collins, 2018
* https://www.benlcollins.com
*/

  var url = "https://docs.google.com/spreadsheets/d/1TLkuDofF1YDxhYiuwyIB7qCJzlAFqho0c6QPXnDPnM8/edit";
  var ss = SpreadsheetApp.openByUrl(url);
  var sheets = ss.getSheets();
  var irTotal = 0;

  sheets.forEach(function(sheet) {
    var r = sheet.getLastRow();
    var c = sheet.getLastColumn();
    var data_counter = r * c;
    var irCounter = 0;

    var IR = /.*IMPORTRANGE.*/;

    if (data_counter !== 0) {

      var dataRange = sheet.getRange(1,1,r,c);
      //Logger.log("sheet = "+sheet.getName()+", range = "+dataRange.getA1Notation());
      var formulaCells = dataRange.getFormulas();

      formulaCells.forEach(function(row) {
        row.forEach(function(cell) {
          if (cell.toUpperCase().match(IR)) { irCounter++ };
        });
      });

      Logger.log("sheet = "+sheet.getName()+", num of 'IMPORTRANGE' formulas = "+irCounter);
      irTotal =irTotal +irCounter  
    }

  }); 
   Logger.log("The total number of IMPORTRANGE formulas is "+irTotal);
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.