如何提高这个谷歌表单自定义功能的性能

Question

我有一个Google Sheets项目，它使用这个自定义函数50-100次，所以我正在努力使该函数尽可能高效。该函数过滤写入INPUT工作表的数据(通过Google API)，然后为数据的子集绘制一个表。

我在这里提供了一个工作示例电子表格：https://docs.google.com/spreadsheets/d/1KVjDl0Ix2bnlPqPEnsY4wh34MIPeayDPk3cq7DdU3g4/edit?usp=sharing

"META"工作表只是通过更改单元格A1中的值来触发自定义函数运行(即模拟通过Google API填充的INPUT工作表)，单元格A1是自定义函数调用的参数。

"INPUT"工作表包含示例输入数据。灰色列(组名称、字段名称、类型、连接Id、VLookup值)不被函数使用：

值连接Id>91c7db0a-c52a-407d-869a-af8ba8bf8ba7字段1货币100>Pilot>>>飞行员文件td>200货币>飞行员文件试点ac64 e01-fe85-400a-92e4-9cebf1c260实例1A货币试点文件>试点>>>试点文件>试点td>220货币>试点文件/td>91c7db0a-c52a-407d-869a-af8ba8bf8ba7Co-PIlot_File联合试点91c7db0a-c52a-407d-869a-af8ba8bf8ba7>Co-PIlot_File联合试点/td>ac64e01-fe85-400a-92e4-69cebf1c260d货币Co-PIlot_File联合导频td>3000

	实例Id	组Id	字段名	字段Id	类型	文件Id		角色	V查找值
Instance1A	小工具	09456c1a-abb4-4e81-94bd-7ce4c88afffc	Pilot_File	100
实例1A	小部件	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	字段2	474f6395-83a7-4c2b-aa5a-ceb00e200f8e	CURRENCY	200
实例1A	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	字段3	ac64e01-fe85-400a-92e4-69cebf1c260d	300	300
实例1B	小部件	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	字段1	09456c1a-abb4-4e81-94bd-7ce4c88afffc	110	109456c1a-abb4-4e 81-94bd-7 ce4c88AFffc实例1B	110
实例1B	小部件	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	字段2	474f6395-83a7-4c2b-aa5a-ceb00e200f8e	CURRENCY	220
实例1B	Widgets	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	字段3	ac64e01-fe85-400a-92e4-69cebf1c260d	330
实例2A	Widgets	字段1	09456c1a-abb4-4e81-94bd-7ce4c88afffc	货币	1000
实例2A	Widgets	字段2	474f6395-83a7-4c2b-aa5a-ceb00e200f8e	货币	2000	2000
实例2A	小部件	91c7db0a-c52a-407d-869a-af8ba8bf8ba7	字段3	3000

当普通的电子表格公式似乎足够时，为什么需要自定义函数还不清楚。尝试使用pivot子句的query()，如下所示：

=query(INPUT!A1:K, "select A, max(G), H where I = 'Pilot' group by A, H pivot D", 1)

=query(INPUT!A1:K, "select A, max(G), H where I = 'Co-Pilot' group by A, H pivot D", 1)

这些公式与您显示的自定义函数结果完全匹配，并且运行速度应该更快。

要对数据进行重新排序，使其符合TABLE_CONFIG工作表中给定的列号，请将第一个query()公式放在单元格query!A1的新工作表中，并将这些公式放在另一个工作表中：

细胞A1:

=arrayformula( 
{ 
"Instance Id", 
iferror( 
vlookup( 
sequence(1, max(TABLE_CONFIG!C2:C4) - 1, 2), 
{ TABLE_CONFIG!C2:C, TABLE_CONFIG!B2:B }, 
2, false 
) 
), 
"File Id" 
} 
)

细胞A2:

=arrayformula( 
iferror( 
hlookup( 
A1:I1, 
query!A1:E, 
sequence(counta(query!A2:A), 1, 2), 
false 
) 
) 
)

公式可以放在一起，这样就不需要辅助表，但这只是读者的练习。这只是为了表明数据可以使用普通的电子表格公式进行聚合，而无需使用脚本。

使用这个漂亮的项目AlaSQLGS~

我们可以使用AlaSQL来简化许多数据操作函数，这些函数(imo)在数据库中比javascript/appscript更好。

下面是一个可以使用您的数据进行修改的示例：

const alasql = AlaSQLGS.load()
function Update_Results() {
const groupUUID = '91c7db0a-c52a-407d-869a-af8ba8bf8ba7';
const role = 'Pilot';
const inputSheetName = 'INPUT';
const outputSheetName = 'RESULTS_Pilot';
const ss = SpreadsheetApp.getActiveSpreadsheet();
const inputSheet = ss.getSheetByName(inputSheetName);
const outputSheet = ss.getSheetByName(outputSheetName)

let data = inputSheet.getDataRange().getValues();
data.shift() // Remove column names~

// Create a Table and insert values into it.
// I use ` so often, because you have Spaces and illegal sql column names.
// Note that these are backticks, not single quotes.
alasql(`
CREATE TABLE data (
`Instance Id` STRING,
`Group Id` STRING,
`Field name` STRING,
`Field Id` STRING,
`Value` INT,
`File Id` STRING,
`Role` STRING
);
SELECT [0] AS `Instance Id`
, [2] AS `Group Id`
, [3] AS `Field name`
, [4] AS `Field Id`
, [6] AS `Value`
, [7] AS `File Id`
, [8] AS `Role`
INTO data
FROM ?;
`, [data])
// Query the Table
// MATRIX gives us an Array of Arrays instead of an Array of Objects.
let output = alasql(`
SELECT MATRIX
`Instance Id`
, `Field name` 
, `File Id`
, [Value]
FROM data
PIVOT (MAX([Value]) FOR `Field name`)
WHERE `Group Id` = ?
AND `Role` = ?
`, [groupUUID, role]);

// Add Column Names
output.unshift(['Instance Id', 'File Id', ...output[0].slice(2).map((x,i) => `Field ${i+1}`)])

// Insert the result into the sheet
outputSheet.clearContents()
outputSheet.getRange(1, 1, output.length, output[0].length).setValues(output);
Logger.log(output)
}

记录器输出：

[[Instance Id, File Id, Field 1, Field 2, Field 3],
[Instance1A, Pilot_File, 100.0, 200.0, 300.0],
[Instance1B, Pilot_File, 110.0, 220.0, 330.0]]

GSheet输出：

实例Id
Instance1A