在谷歌应用程序脚本上使用for循环进行Vlookup

Question

我需要你的帮助。我想做一个for循环或其他类似公式=Vlookup 的东西

我有两张床单。在Sheet1(概述(中，有类似于1000、1002、1003、…的ID，。。。，A列100X；

Sheet2是一个表单响应表(响应(，您需要在其中输入您的ID和带有"Ok"one_answers"Nok"的Action。我输入的ID显示在表2的B栏中，操作(确定/否(显示在表2中的C栏中。

现在我想用onFormSubmit函数将Ok/Nok复制到概览表中具有相同ID的行。

例如。ID为1005的人员以"Ok"作为表单响应。现在应该将"Ok"复制到Overview工作表的B列和确切的行中(在本例中：ID为1005的行(。

这是我的功能。但我不想在表格中有公式。所以我寻求另一个解决方案。

function vlookup() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange(1,5);
cell.setFormula('=ARRAYFORMULA(IFS(ROW(Response!B:B)=1,"Action from 
User",Response!B:B="","",TRUE,IFERROR(VLOOKUP(A:A,Response!B:C,2,0),"Waiting for Response")))');
}

希望有人能帮助我。提前感谢您的帮助！

乔纳斯

Answer 1

感谢您的回答。我尝试了stackoverflow.com/questions/60255775–TheMaster上的代码，效果很好！

但对于编程初学者来说，它非常复杂。特别是带有"；散列"；。我还添加了第二个比较和复制，以便在表单中使用Nok的情况下从Reason中获取数据。

const ss = SpreadsheetApp.getActive();
/**
* @param {GoogleAppsScript.Spreadsheet.Sheet} fromSht -Sheet to import from
* @param {GoogleAppsScript.Spreadsheet.Sheet} toSht -Sheet to import to
* @param {Number} fromCompCol -Column number of fromSht to compare
* @param {Number} toCompCol -Column number of toSht to compare
* @param {Number} fromCol -Column number of fromSht to get result
* @param {Number} toCol -Column number of toSht to get result
*/
function copyToOverview(e,response,
fromSht = ss.getSheetByName('Response'),
toSht = ss.getSheetByName('Overview'),
fromCompCol = 2,
toCompCol = 1,
fromCol = 3,
toCol = 2,
fromColRej = 4,
toColRej = 3

) {
const toShtLr = toSht.getLastRow();
const toCompArr = toSht.getRange(2, toCompCol, toShtLr - 1, 1).getValues();
const fromArr = fromSht.getDataRange().getValues();
fromCompCol--;
fromCol--;
fromColRej--;
/*Create a hash object of fromSheet*/
const obj1 = fromArr.reduce((obj, row) => {
let el = row[fromCompCol];
el in obj ? null : (obj[el] = row[fromCol]);
return obj;
}, {});

/*Create a second hash object of fromSheet to copy the Reason why it is Nok (also from filling out the Form) */
const obj3 = fromArr.reduce((obj2, row) => {
let el1 = row[fromCompCol];
el1 in obj2 ? null : (obj2[el1] = row[fromColRej]);
return obj2;
}, {});


//Paste to column  first toSht copy the "ok/nok" second toSht for the Reason why Nok
toSht
.getRange(2, toCol, toShtLr - 1, 1)
.setValues(toCompArr.map(row => (row[0] in obj1 ? [obj1[row[0]]] : [null])));

toSht
.getRange(2, toColRej, toShtLr - 1, 1)
.setValues(toCompArr.map(row => (row[0] in obj3 ? [obj3[row[0]]] : [null])));


}

我还尝试了来自"；Michiel the Temp"；它能缝合，也能工作。

来自"；Mateo Randwolf"；看起来很简单，我也试过了。工作也很好！我对它做了一点修改，它像我希望的那样工作！我想我会使用这个代码。

function onFormSubmit(e) {
// Get the sheet where the form responses are submitted and the one where we want to check the IDs
var formSheet = SpreadsheetApp.getActive().getSheetByName('Response');
var destinationSheet = SpreadsheetApp.getActive().getSheetByName('Overview');

// Get the new incoming data (ID and Ok/Nok) with each form submit by accessing  
// the trigger object e which is the submited and new form response row
var submittedId = formSheet.getRange(e.range.getRow(), 2).getValue();
var submittedValue = formSheet.getRange(e.range.getRow(), 3).getValue();
var submittedValueReason = formSheet.getRange(e.range.getRow(), 4).getValue();


// get all the ID values we have in the sheet we want to check them. flat will convert all the returning
// 2D array of values in a 1D array with all the IDs
var idRange = destinationSheet.getRange(1, 1, destinationSheet.getLastRow(),1).getValues().flat();

// iterate over all your IDs
for(i=0;i<idRange.length;i++){
// if one ID is the same as the incoming one from the form response
if(idRange[i] == submittedId){
// set its value to the one submitted by the form
destinationSheet.getRange(i+1, 2).setValue(submittedValue);
}        

if(idRange[i] == submittedId){

destinationSheet.getRange(i+1, 3).setValue(submittedValueReason);
destinationSheet.getRange(i+1, 2).getValue() == "Nok" ? destinationSheet.getRange(i+1, 4).setValue("Closed") : destinationSheet.getRange(i+1, 4).setValue("Open");
}   

}

}

谢谢大家的帮助，你们太棒了！

因此，我可以通过更新Form中的复选框来完成项目的下一步。

Answer 2

我没有用触发器测试它，但它应该可以工作

function vlookup() {
var ssOverview = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Overview");
var ssOverviewLr = ssOverview.getLastRow();
var ssOverviewData = ssOverview.getRange(2, 1, ssOverviewLr, 1).getValues(); //assuming you have a header in the first row

var ssResponse = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Response");
var ssResponseLr = ssResponse.getLastRow();
var newResponse = ssResponse.getRange(ssResponseLr, 2, 1, 2).getValues(); 

var Ids = ssOverviewData.map(function (r){return r[0];});


for(var i = 0; i < newResponse.length; i++) 
{
var row = newResponse[i];
var id = row[0];
var action = row[1];

var index = Ids.indexOf(id);

if(index == -1)
{
SpreadsheetApp.getActiveSpreadsheet().toast("No matches", "Be aware")
}
else
{
ssOverview.getRange(index + 2, 2).setValue(action); //this puts the action in column B
}
}
}

Answer 3

为了在每次提交新表单时检查ID并相应地更改ID表中的数据，您需要使用可安装的触发器。具体来说，您应该使用FormSubmit触发器，该触发器在每次提交表单时都会触发该函数。与此触发器一起使用的还有它的事件对象。

要添加可安装的触发器，请在应用程序脚本编辑器中转到编辑->当前项目的触发器，然后单击Add trigger创建一个新触发器。请确保选择On form submit作为事件类型，并选择下面提供的函数(因此，在创建触发器之前，请先复制/粘贴下面的函数(。

以下函数使用此触发事件将传入数据与ID的列A进行比较，并检查是否匹配，如果匹配，则添加相关的Ok/Nok信息。它有不言自明的评论：

function onFormSubmit(e) {
// Get the sheet where the form responses are submitted and the one where we want to check the IDs
var formSheet = SpreadsheetApp.getActive().getSheetByName('Form Responses 1');
var destinationSheet = SpreadsheetApp.getActive().getSheetByName('Check');

// Get the new incoming data (ID and Ok/Nok) with each form submit by accessing  
// the trigger object e which is the submited and new form response row
var submittedId = formSheet.getRange(e.range.getRow(), 2).getValue();
var submittedValue = formSheet.getRange(e.range.getRow(), 3).getValue();

// get all the ID values we have in the sheet we want to check them. flat will convert all the returning
// 2D array of values in a 1D array with all the IDs
var idRange = destinationSheet.getRange(1, 1, destinationSheet.getLastRow(),1).getValues().flat();

// iterate over all your IDs
for(i=0;i<idRange.length;i++){
// if one ID is the same as the incoming one from the form response
if(idRange[i] == submittedId){
// set its value to the one submitted by the form
destinationSheet.getRange(i+1, 2).setValue(submittedValue);
}
}

}