Google Sheets IMPORTRANGE function: How can I find what documents are connected to my source?

Question

I searched Google and specifically Stack Overflow for this information but was unable to find an answer. I apologize if I missed it due to using the wrong search terms or some other silly reason.

I have connected many tracking documents to a primary source 'directory' document using vlookup/query combined with importrange to pull contact information, relevant leadership, etc. We are overhauling these connections to be based on unchanging employee/location codes as opposed to by name as it's done currently. My back-burnered ToDo to maintain a list of connected documents has now come to bite me.

Is there an efficient way to find what documents are connected to my source? Something like a list of all other documents using importrange to 'look' at my source document.

Thank you for your time

Answer 1

To be completly honest, you seem to need a database. That being said you can programatically all the URLs used in IMPORTRANGE like so:

function findImportUrls() {
  const ss = SpreadsheetApp.openByUrl('URL of a sheet')
  const result = new Set();

  for (let sheet of ss.getSheets()) {
    // Find any cell with importrange in their formula
    const ranges = sheet.createTextFinder("importrange")
      .matchFormulaText(true)
      .matchCase(false)
      .findAll()
    
    // Go to each cell and extract URLs with regex
    for (let range of ranges) {
      getRangeImportUrls(range, result)
    }
  }

  // Print allthe URLs to console
  for (let url of result.values()) {
    console.log(url)
  }
}

function getRangeImportUrls(range, resultSet) {
  const formula = range.getFormula()
  if (!formula) return

  // Use regular expressions to extract any importrange url
  const regex = /importrange\("(.+?)"/gmi
  let m
  while ((m = regex.exec(formula)) !== null) {
      // Get the first group and add it to the result set
      resultSet.add(m[1])
  }
}

This uses the spreadsheet search functionality to find any formula with IMPORTRANGE. Then we use regex to extract the value from the formula. The finder may add ranges that contain the word importrange but they are not formulas. On those cases, the regex won't find anything and thus will be ignored.

The regex itself simply matches any text that is importrange( followed by text in double quotes. It saves the text inside the quotes as a group (to be extracted).

Note that this only gets the URLs from a single spreadsheet.

References

• Sheet.createTextFinder(findText) (Apps Script reference)
• Class TextFinder (Apps Script reference)
• Regular expressions (MDN guide)