Vlookup at Google Apps Script with for loop

Question

I need your help please. I would like to do a for loop or something else that works like a Formula =Vlookup

I have two Sheets. in Sheet1 (Overview) there are ID's like 1000, 1002, 1003,...,100X in Column A;

Sheet2 is a Form Response Sheet (Response), where you need to enter your ID and an Action with 'Ok' and 'Nok'. The ID I enter appears in Sheet2 Column B and the Action (Ok/Nok) apperas in Sheet2 Column C.

Now I would like to Copy the Ok/Nok to the Row with the same ID in the Overview sheet with a onFormSubmit function.

for Example. Person with ID 1005 makes a form response with the Action 'Ok'. Now should the 'Ok' copied to the Overview sheet in Column B and in the exact row (in this case: row with the ID 1005).

Here is my function. but I don't want to have formulars in the sheet. so I aked for another solution.

 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")))');
 }

Hope someone can help me. Thank you in advance for your help!

Jonas

See https://stackoverflow.com/questions/60255775/ – TheMaster Dec 07 '20 at 13:55 — TheMaster, Dec 07 '20 at 13:55

score 0 · Answer 1 · edited Dec 10 '20 at 20:24

I didn't test this with a trigger but this should work

 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
     }
   }
 }

score 0 · Answer 2 · edited Dec 12 '20 at 20:20

In order to check the IDs every time there is a new form submission and change the data in the ID sheet accordingly you will need to use installable triggers. Specifically you should use a FormSubmit trigger which triggers the function every time there is a form submission. Along with this trigger you will use its event object.

To add an installable trigger, in your Apps Script editor go to Edit -> Current project's triggers and create a new trigger by clicking Add trigger. Make sure that you select On form submit as the event type and that you select the function presented below (so please first copy/paste the function below before creating your trigger).

The following function takes use of this trigger event to compare the incoming data to your Column A of IDs and check for matches and if so it adds the relevant Ok/Nok information. It has self explanatory comments:

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);
    }
  }
  
}

score 0 · Answer 3 · answered Dec 09 '20 at 08:45

Thank you for all these answers. I tryed the code from stackoverflow.com/questions/60255775 – TheMaster and that workes fine!

but it seams very complicated for a programming beginner. exspecially the part with the "Hash". I also added a second compare and copy to get the data from a Reason if the Nok is used in the Form.

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])));
    
    
}

I also tried the Code from "Michiel the Temp" and it seams, that it also works.

The code from "Mateo Randwolf" looks very simple and I tried it too. Works also very good! I have modified it a bit and it works like I wish! I think I will use this code.

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");
    }   
      
  }
  
}

Thank you all for the Help you are amazing!

So I can do my next step in the Project with updating checkboxes in the Form.

Vlookup at Google Apps Script with for loop

3 Answers3