如何通过许多importrange加快图纸加载时间? [英] How do I speed up Sheet load times with a lot of importranges?

问题描述

我已经晋升为开发&在工作中维护Google表格数据库.我对Google表格的脚本和&知之甚少.从四处询问，并研究它看起来像是GAS可能是我需要开始前进的途径.

I've been promoted into developing & maintaining a Google Sheets database at work. I know very little about Google Sheets scripting & from asking around, and researching it's looking like GAS is probably the avenue that I need to start heading down.

因此，我们在Google表格中有3个工作簿；2包含大量数据，另一个工作簿为我们的销售dpt提供了一个UI.访问数据.我真的希望能与您分享这些内容，因为很难描述它们.在UI工作簿中，单独的页面与一个数据库中的工作表配对(我们称其为数据库A).

So we have 3 Workbooks in Google Sheets; 2 contain large amounts of data, the other workbook provides a UI for our sales dpt. to access the data. I really wish I could share these with you, as describing them is difficult. In the UI workbook, separate pages are paired with sheets in the one database (lets call it database A).

推销员将转到其销售设备所用产品的UI表；工作表的顶部实际上允许他从数据库A中选择一行.做出选择后，工作表的其余部分将填充我们生产的，可以在顶部进行选择的产品.我们生产的产品存储在另一个数据库("B")中.我们必须有两个数据库，因为我们早先已经将两个数据库结合在一起达到了表格中的单元格限制.

A salesman will go to the UI sheet for the product he's selling a device for; the top section of the sheet allows him to select, essentially, a row from database A. After the selection is made, the rest of the sheet is populated with products we manufacture that work with the choice made in the top section; the products we make are stored in the other database ("B"). We have to have two databases, as we've earlier hit the cell-limit in sheets with the two databases combined.

每个UI页面平均大约有150个Importranges.通过查询查找完成.

On average each UI page has about 150 Importranges. Looking up done with Query.

我们的问题是，此UI变得非常慢，初始加载时间使路上的销售人员变得一文不值，并且使办公室的销售人员烦恼.进行初始选择(查询数据库A)时的延迟是可用的，但仍然比我们想要的慢得多.而且我们还没有完成UI页面的构建.

Our problem is that this UI is getting pretty slow, initial load time makes it worthless for salesmen on the road, and annoying to the salesmen here in the office. The delay when making the initial selections (querying database A) is usable, but still much slower then we'd like. And we're not finished building UI pages.

我在线上看到大多数人建议使用Apps Script代替importrange，但是对Apps Script一无所知，我无法理解正在执行的操作，或者主要是如何获取Apps脚本并实际放置单元格中的数据.

I've seen online that most people recommend using Apps Script to replace importrange, but knowing nothing about Apps Script, I haven't been able to understand what is being done, or mainly how to take the apps script and actually put the data in the cells.

因此，我希望能获得任何帮助以加快速度.

推荐答案

使用UI的Google Apps脚本提供的WebApp可能是最好的选择，如果您有一些示例数据，我很乐意为您提供帮助.如果您仍要使用工作表，则可以使用每隔10分钟左右运行一次的Google Apps脚本函数替换importRanges，以保持用户界面工作表的更新.它应该加快加载时间.这样的事情将为您工作:

You are probably best off using a WebApp served from Google Apps Script for the UI which I'd be happy to help with if you had some sample data. If you wanted to still use the sheets, then you could replace the importRanges with some Google Apps Script function that runs every 10 minutes or so to keep the UI sheet updated. It should speed up load times. Something like this would work for you:

function importSheetA() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var database = SpreadsheetApp.openByUrl("DATABASE_A_URL");
var dataToCopy = database.getSheetByName("DATABASE_A_SHEET_NAME").getDataRange().getValues();
var copyToSheet = ss.getSheetByName("UI_SHEET_NAME");
var copyData = copyToSheet.clearContents().getRange(1, 1, dataToCopy.length, dataToCopy[0].length).setValues(dataToCopy);
}

这篇关于如何通过许多importrange加快图纸加载时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！