更好/更快的方式传递50+值从一个谷歌表到另一个

Question

我是一个全新的App Script，所以请原谅我的无知。

我用来保存学生数据的Google表格太长太笨重了(50多个列)，所以我决定创建另一个表格作为数据输入的前端。通过几个小时的教程视频+笨拙的试验和错误，我想出了一个工作脚本，从我的数据输入表单样表("学生")中获取值，并将这些值传递到我的目标/容器表("主")中的第一个空行。

我对脚本的工作方式非常满意——除了它的速度慢得离谱。根据我所读到的，我认为我对Sheets API进行了太多的调用，我需要弄清楚如何将所有值从"Students"传递给"Master"，而不是一个接一个地传递，但我没有这样做的技能，而且我似乎找不到一个例子。

我肯定有一个非常简单，优雅的解决方案。有人能帮忙吗?

下面是我的一小段代码(希望它足以看出我使用的低效策略):

function submitStudentData(){
var caseloadManager = SpreadsheetApp.getActiveSpreadsheet();
var enterStudents = caseloadManager.getSheetByName('Students');
var masterSheet = caseloadManager.getSheetByName('Master');
var clearFields = enterStudents.getRangeList(['C6:C18', 'C22', 'E6:E18','G6:G14','G20','I6:I14','K6:K16', 'M6:M18']);
var blankRow = masterSheet.getLastRow()+1;
masterSheet.getRange(blankRow,1).setValue(enterStudents.getRange("Z1").getValue()); //Concatenated Student Name
masterSheet.getRange(blankRow,3).setValue(enterStudents.getRange("C6").getValue()); //First Name
masterSheet.getRange(blankRow,2).setValue(enterStudents.getRange("C8").getValue()); //Last Name
masterSheet.getRange(blankRow,4).setValue(enterStudents.getRange("C10").getValue()); //Goes By
masterSheet.getRange(blankRow,6).setValue(enterStudents.getRange("E6").getValue()); //DOB
masterSheet.getRange(blankRow,7).setValue(enterStudents.getRange("E8").getValue()); //Grade
masterSheet.getRange(blankRow,5).setValue(enterStudents.getRange("E10").getValue()); //Student ID
masterSheet.getRange(blankRow,10).setValue(enterStudents.getRange("E14").getValue()); //Last FIE
masterSheet.getRange(blankRow,11).setValue(enterStudents.getRange("Z2").getValue()); //Calculated FIE Due Date
masterSheet.getRange(blankRow,8).setValue(enterStudents.getRange("E12").getValue()); //Last Annual Date[enter image description here][1]
masterSheet.getRange(blankRow,13).setValue(enterStudents.getRange("G6").getValue()); //PD
masterSheet.getRange(blankRow,14).setValue(enterStudents.getRange("G8").getValue()); //SD
masterSheet.getRange(blankRow,15).setValue(enterStudents.getRange("G10").getValue()); //TD
masterSheet.getRange(blankRow,16).setValue(enterStudents.getRange("G3").getValue()); //Concatenated Disabilities
masterSheet.getRange(blankRow,18).setValue(enterStudents.getRange("G12").getValue()); //Program Type
masterSheet.getRange(blankRow,12).setValue(enterStudents.getRange("G14").getValue()); //Evaluation Status
masterSheet.getRange(blankRow,20).setValue(enterStudents.getRange("I6").getValue()); //DYS
masterSheet.getRange(blankRow,21).setValue(enterStudents.getRange("I8").getValue()); //GT
masterSheet.getRange(blankRow,19).setValue(enterStudents.getRange("I10").getValue()); //EB
masterSheet.getRange(blankRow,24).setValue(enterStudents.getRange("I12").getValue()); //ESY
masterSheet.getRange(blankRow,22).setValue(enterStudents.getRange("I14").getValue()); //BIP
masterSheet.getRange(blankRow,29).setValue(enterStudents.getRange("K6").getValue()); //TR
masterSheet.getRange(blankRow,30).setValue(enterStudents.getRange("K8").getValue()); //OT

在清除'Students '中的所有字段之前，继续这样进行52个值。它可以工作，但是运行需要一分钟多的时间。

我想附上一张我的"学生"表单样表格的图片，以防我的描述不清楚。

非常感谢你帮助一个不知道自己在做什么的卑微的特殊教育工作者。:)学生表格/表格图像

Answer 1

阅读最佳实践即使你的数据不是一个连续的范围，它是一个的一部分，所以用getValues()获得整个范围，并使用适当的索引来访问你想要的。最终会快得多。您可能不希望使用setValues来写入数据，因为存在混淆公式等其他问题。尽可能避免使用setValue()和getValue()

function submitStudentData() {
const ss = SpreadsheetApp.getActive();
const ssh = ss.getSheetByName('Students');
const msh = ss.getSheetByName('Master');
const nr = msh.getLastRow() + 1;
const vs = ssh.getRange(nr, 1, ssh.getLastRow(), ssh.getLastColumn()).getValues();
let oA1 = [[vs[0][25], vs[7][2], vs[5][2], vs[9][2], vs[9][4], vs[5][4], vs[7][4], vs[11][4]]];
msh.getRange(msh.getLastRow() + 1, 1, oA1.length, oA[0].length).setValues(oA1);//This line replaces all of the below lines
msh.getRange(nr, 1).setValue(vs[0][25]);//Concatenated Student Name
msh.getRange(nr, 2).setValue(vs[7][2]); //Last Name
msh.getRange(nr, 3).setValue(vs[5][2]); //First Name
msh.getRange(nr, 4).setValue(vs[9][2]); //Goes By
msh.getRange(nr, 5).setValue(vs[9][4]); //Student ID
msh.getRange(nr, 6).setValue(vs[5][4]); //DOB
msh.getRange(nr, 7).setValue(vs[7][4]); //Grade
msh.getRange(nr, 8).setValue(vs[11][4]); //Last Annual Date[enter image description here][1]

您也可以通过使用公式将所有数据映射到单行或列来做类似的事情，从而使运行脚本更容易。

Answer 2

下面是工作示例。只需按照代码中所描述的完成mapping数组。运行时间小于1秒

const mapping= [
// enter the array [ sourceRange, destinationRow ] for each cell you want to copy form Students to Master
['Z1',1],
['C6',3],
['C8',2],
['C10',4],
['E6',6]
// ... and so on
]
function submitStudentData() {
console.time('submitStudentData')
const caseloadManager = SpreadsheetApp.getActive();
const enterStudents = caseloadManager.getSheetByName('Students');
const masterSheet = caseloadManager.getSheetByName('Master');
const data = enterStudents.getDataRange().getValues()
const destRow = []
mapping.forEach((m,i)=>{
[rowi,coli] = rangeToRCindex(m[0])
const destRowIndex = m[1] - 1
destRow[destRowIndex] = data[rowi][coli]
})
masterSheet.appendRow(destRow)
console.timeEnd('submitStudentData')
}
function rangeToRCindex(range){
const match = range.match(/^([A-Z]+)(d+)$/)
if (!match) {
throw new Error(`invalid range ${range}`)
}
const col = letterToColumn(match[1])
const row = match[2]
return [row-1,col-1]
}
function letterToColumn(columnLetters) {
let cl = columnLetters.toUpperCase()
let col = 0
for (let i = 0; i < cl.length; i++) {
col *= 26
col += cl.charCodeAt(i) - 65 + 1
}
return col
}

Answer 3

就像Cooper说的，你要尽可能避免阅读和书写表单。(我有同样的问题，当我开始使用谷歌脚本)

这意味着您应该将整个范围读入一个变量，然后将您的行写入母表。

下面是一个例子，你可以使用它来避免setValue()和getValue()慢你正在经历

function submitStudentData(){
var caseloadManager = SpreadsheetApp.getActiveSpreadsheet();
var enterStudents = caseloadManager.getSheetByName('Students');
var masterSheet = caseloadManager.getSheetByName('Master');
var clearFields = enterStudents.getRangeList(['C6:C18', 'C22', 'E6:E18','G6:G14','G20','I6:I14','K6:K16', 'M6:M18']);
var blankRow = masterSheet.getLastRow()+1; //You will not need this

//First we will all the data from the students sheet. This will make and array of arrays [[row],[row],[row]]. 
studentData = enterStudents.getRange(1,1,enterStudents.getLastRow(),enterStudents.getLastColumn()).getValues()
Logger.log(studentData)
//We are going to build an array of arrays of the data that we want to write back to the master sheet. We will start by creating our first array
writeData = []
//Then we loop through all the student data
for (var i = 0; i < studentData.length; i++) { 
Logger.log(studentData[i][0])
//We are selecting data from each row to add to our array. in "studentData[i][0]" the [0] is the column number (remember we are starting with 0)
rowData = []
rowData.push(studentData[i][0])
rowData.push(studentData[i][2])
rowData.push(studentData[i][1])
//Then we send the full row to the first array we made
writeData.push(rowData)
}

Logger.log(writeData)
// Now to write out the data. Normally it would not be a good idea to loop a write like this but this as an atomic operation that google will automatically batch write to the sheet. 
for (var i = 0; i < writeData.length; i++) { 
masterSheet.appendRow(writeData[i])
}
}

希望这能帮助你开始。