英文:
App Script in Google Sheets - problem with data auto complete
问题
以下是您要翻译的内容:
"I have two sheets. In one, the data of the manufactured devices is already entered. In the second, new warranty claims are added daily. To shorten the working time, I automatically copy the production data from the sheet Production to sheet Complaints. Currently I am solving this with VLOOKUP or INDEX function. But it works very slow when number of rows >20k.
Is it possible to write a script (using AppScripts) where after entering the ID number in the complaint sheet and selecting this cell, script will independently fill in the appropriate columns with data from 'Production'? (the order of the columns is different in both sheet)
I've tried all option with built-in function, I'm expecting to know and understand a possible solution using AppScript"
英文:
I have two sheets. In one, the data of the manufactured devices is already entered. In the second, new warranty claims are added daily. To shorten the working time, I automatically copy the production data from the sheet Production to sheet Complaints. Currently I am solving this with VLOOKUP or INDEX function. But it works very slow when number of rows >20k.
Is it possible to write a script (using AppScripts) where after entering the ID number in the complaint sheet and selecting this cell, script will independently fill in the appropriate columns with data from 'Production'? (the order of the columns is different in both sheet)
I've tried all option with built-in function, I'm expecting to know and understand a possible solution using AppScript
答案1
得分: 0
在你的情况下,以下是一个示例脚本,你可以复制并粘贴到电子表格的脚本编辑器中并保存脚本。请再次确认工作表的名称。
```javascript
function myFunction() {
// 检索工作表。
const ss = SpreadsheetApp.getActiveSpreadsheet();
const productionSheet = ss.getSheetByName("Production");
const complaintsSheet = ss.getSheetByName("Complaints");
// 从工作表中检索值。
const [h1, ...v1] = productionSheet.getDataRange().getValues();
const range = complaintsSheet.getRange("A2:I" + complaintsSheet.getLastRow());
const [h2, ...v2] = range.getValues();
// 创建一个数组,通过转换列来放入“Complaints”工作表。
const hconv = h2.map(h => h1.indexOf(h));
const obj = v1.reduce((o, r) => (o[r[0]] = hconv.map(i => i > -1 ? r[i] : null), o), {});
const len = Object.values(obj)[0].length;
const res = v2.map(([a]) => obj[a] || Array(len).fill(null));
// 将创建的数组放入“Complaints”工作表。
range.offset(1, 0, res.length, res[0].length).setValues(res);
}
注意:
-
在这个示例中,我使用了你的示例电子表格进行了测试。如果你更改电子表格,这个脚本可能无法使用。请注意这一点。
-
我认为在这种情况下,可能可以使用自定义函数。但根据你的问题,如果数据很大,可能不能使用自定义函数。因此,我提出了上述脚本。
-
在这个示例中,为了转换列,使用了标题。所以,如果你更改标题,请注意这一点。
-
另一种方法是,如果你想在将值放入“Complaints”列“A”时将值放入行,可以尝试以下脚本。在这个脚本中,通过OnEdit的简单触发器自动运行脚本。因此,在这种情况下,当你将值放入“Complaints”工作表的列“A”时,这个脚本会自动运行,并将值放入编辑的行。请根据你的实际情况选择其中一个脚本。一个重要的注意事项是,在使用时,请不要直接运行这个函数,因为没有事件对象。请注意这一点。
function onEdit(e) {
const { range, source } = e;
const complaintsSheet = range.getSheet();
if (complaintsSheet.getSheetName() != "Complaints" || range.columnStart != 1 || range.rowStart <= 2) return;
const value = range.getValue();
const productionSheet = source.getSheetByName("Production");
const [h1, ...v1] = productionSheet.getDataRange().getValues();
const [h2] = complaintsSheet.getRange("A2:I2").getValues();
const hconv = h2.map(h => h1.indexOf(h));
const obj = v1.reduce((o, r) => (o[r[0]] = hconv.map(i => i > -1 ? r[i] : null), o), {});
const len = Object.values(obj)[0].length;
const res = [obj[value] || Array(len).fill(null)];
range.offset(0, 0, res.length, res[0].length).setValues(res);
}
英文:
In your situation, how about the following sample script?
Sample script:
Please copy and paste the following script to the script editor of Spreadsheet and save the script. And please confirm the sheet names again.
function myFunction() {
// Retrieve sheets.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const productionSheet = ss.getSheetByName("Production");
const complaintsSheet = ss.getSheetByName("Complaints");
// Retrieve values from sheets.
const [h1, ...v1] = productionSheet.getDataRange().getValues();
const range = complaintsSheet.getRange("A2:I" + complaintsSheet.getLastRow());
const [h2, ...v2] = range.getValues();
// Create an array for putting to Complaints sheet by converting columns.
const hconv = h2.map(h => h1.indexOf(h));
const obj = v1.reduce((o, r) => (o[r[0]] = hconv.map(i => i > -1 ? r[i] : null), o), {});
const len = Object.values(obj)[0].length;
const res = v2.map(([a]) => obj[a] || Array(len).fill(null));
// Put the created array to Complaints sheet.
range.offset(1, 0, res.length, res[0].length).setValues(res);
}
- When this script is run, the values are retrieved from both sheets. And, an array is created for putting to "Complaints" sheet by checking the ID of column "A" and converting the columns. And, the array is put to "Complaints" sheet.
Note:
-
In this sample, I tested it using your sample Spreadsheet. If you change the spreadsheet, this script might not be able to be used. Please be careful about this.
-
I thought that in this case, the custom function might be able to be used. But, from your question, if the data is large, the custom function cannot be used. So, I proposed the above script.
-
In this sample, in order to convert the columns, the header titles are used. So, if you change the header titles, please be careful about this.
-
As another approach, if you want to put the value to the row when you put a value to the column "A" of "Complaints", how about the following script? In this script, the script is automatically run by a simple trigger of OnEdit. So, in this case, when you put a value to the column "A" of "Complaints" sheet, this script is automatically run, and the value is put into the edited row. Please select one of 2 scripts for your actual situation. As an important point, when you use this, please don't directly run this function because of no event object. Please be careful about this.
function onEdit(e) { const { range, source } = e; const complaintsSheet = range.getSheet(); if (complaintsSheet.getSheetName() != "Complaints" || range.columnStart != 1 || range.rowStart <= 2) return; const value = range.getValue(); const productionSheet = source.getSheetByName("Production"); const [h1, ...v1] = productionSheet.getDataRange().getValues(); const [h2] = complaintsSheet.getRange("A2:I2").getValues(); const hconv = h2.map(h => h1.indexOf(h)); const obj = v1.reduce((o, r) => (o[r[0]] = hconv.map(i => i > -1 ? r[i] : null), o), {}); const len = Object.values(obj)[0].length; const res = [obj[value] || Array(len).fill(null)]; range.offset(0, 0, res.length, res[0].length).setValues(res); }
References:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论