有没有办法查看所有链接/依赖另一个的谷歌表格?

Question

我知道我正在做的事情不好，我们正在努力让它变得更好，但与此同时，我需要凑合着用我所拥有的......

我有一个相当大的谷歌表格，或多或少充当数据库。它从 GForms 提供数据，然后将这些数据用于许多其他工作表，用于各种目的(例如报告、选择性信息共享给某些组等(，主要是通过query和importranges。

主表的编辑仅限于少数人，包括我自己。其中一个进行了一些更改(删除了一些列(，这些更改将破坏任何查询或导入，因为importrange不会捕获列更改并自动更新范围。

所做的更改实际上很好，值得保留，所以我不想还原它们。

有没有办法让我轻松找到所有使用主工作表的工作表，以便我可以更新受影响的公式？在更新时，我还需要找到一种方法来避免再次发生这种情况(我想我可以通过使用address设置范围引用来做到这一点？但那是未来我的问题...

提前感谢！

Answer 1

查找 (Ctrl-H( 提供"也在公式中搜索"的选项

您可以使用它来搜索对特定工作表的引用，例如搜索"sheet1！"将找到引用工作表1的所有单元格

请注意，如果定义了命名范围，这可能不起作用，您可能还需要搜索命名范围。

Answer 2

要查找不同文档(甚至同一文档(中工作表之间的依赖关系，请使用以下脚本搜索对给定文档 ID 的引用(例如在 IMPORTRANGE 中(，例如：

FindFormulae("1XgTuET_dJKP-i4JppvQQFI047-ZKP-i4o4bhE-K1lF-o")

function FindFormulae(keyword) {
// searches all google docs for formulae containing the given keyword
// outputs to sheet "output"
// may take a while if you have a lot of docs, and could timeout (5 minutes)
// if timeout just run the script again, it skips any docs already checked
// to cancel script execution go to https://script.google.com/home/executions
// Feb 2020 www.enex.net 
var sheetOut= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("output");
docsAlreadyChecked = sheetOut.getRange(1,1,sheetOut.getLastRow(),1).getValues();
var files = DriveApp.getFiles();
while (files.hasNext()) {  // loop thru files
var file = files.next();
if (file.getMimeType() != "application/vnd.google-apps.spreadsheet") continue;  // only process spreadsheets
docURL =file.getUrl();
docName =file.getName();
//Logger.log('Starting.. ' + docName + 'n' + docURL + 'n');
//DaysSinceUpdate = (new Date() - file.getLastUpdated())/(1000 * 60 * 60 * 24)
//if ( DaysSinceUpdate > 90 ) continue;  // skip if file hasn't been updated for more than 7 days   
if (docsAlreadyChecked.toString().indexOf(docName)>0) continue;  // skip if already done    // note this needs fixing as will fail if one doc is substring of another
var ssIn = SpreadsheetApp.openByUrl(docURL); // open doc
for (var i = 0; i < ssIn.getNumSheets(); i++) {  //loop thru sheets in doc
sheetname = ssIn.getSheets()[i].getName();
//sheetid = ssIn.getSheets()[i].getSheetId();
if (ssIn.getSheets()[i].getMaxRows()==0) continue; //skip if no rows (eg if it's a chart)
ListFormulae(docURL, sheetname, keyword);
} // end for loop thru sheets
//strHyperlink = "=hyperlink("" + docURL + "#gid=" + ssIn.getSheets()[i].getSheetId() + "", "" + docName + "")";
strHyperlink = "=hyperlink("" + docURL  + "", "" + docName + "")";
sheetOut.appendRow([strHyperlink, '', '', 'finished search ' + new Date() ]);
} // end while loop thru files
}
function ListFormulae(docURL, sheetname, keyword) {

var ssIn = SpreadsheetApp.openByUrl(docURL); 
var sheetIn = ssIn.getSheetByName(sheetname);
//var sheetIn = ssIn.getSheetId(sheetid);
//var sheetname = ssIn.getSheetName();
var sheetOut= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("output");
LastRow = sheetIn.getLastRow();
LastColumn = sheetIn.getLastColumn();
if (LastRow==0 && LastColumn==0)  return;  // nothing in sheet 
var range = sheetIn.getRange(1, 1,LastRow , LastColumn);
var formulas = range.getFormulas();
for (var i in formulas) {
for (var j in formulas[i]) {
if (formulas[i][j]=="") continue;  // skip if no formula
if (formulas[i][j].search(keyword) == -1) continue;  //skip if keyword not found
strHyperlink = "=hyperlink("" + docURL + "#gid=" + sheetIn.getSheetId() + "", "" + ssIn.getName() + "")";
rownum = parseInt(i)+1;
colnum = parseInt(j)+1;
sheetOut.appendRow([strHyperlink, sheetname, 'R' + rownum + 'C' + colnum, "'" + formulas[i][j]]);
}
}
}