Google Sheets - Copy & Paste Data based on 2 things, Game # and Name

Question

been trying to figure out a way to copy and paste data based on both the Game Number entered and Player Name. To break it down, a game has only 4 players and scorekeeping is built on a template on Sheet 1. Sheet 2 is a conglomerate of 16 different players and displays the Game Number and date from when the game was played.

Here is where I am having trouble. In the template, I essentially enter the scores manually as well as the Game Number. I am trying to make a macro that once I press it, it will scan the Game Number on Sheet 1 as a string and identify the matching cell in Sheet 2. It will then proceed to copy and paste the date. The script will then recognize the Player Name row in Sheet 1 and copy and paste the corresponding scores on Sheet 2, in the same row identified earlier by the Game Number. This is really tricky because it has to avoid pasting in the other 12 of 16 players column.

Please take the following guidelines into consideration: stackoverflow.com/help/minimal-reproducible-example — bad_coder
Hello @Cooper thank you for your response! Please find a sample spreadsheet here: docs.google.com/spreadsheets/d/… It would recognize the range "H1" and range "B1:E1" from Sheet1 and paste the range "B5:E5" into Sheet2 according to the name and game # matched — Rayneth Law

Cooper Cooper · Accepted Answer · 2020-01-23T06:58:24

This function collects the score on sheet one and distributes them to the correct row based upon the game number and the correct columns based upon the header strings which correllate to players.

Please be aware that you cannot run this function from the script editor as it requires the onEdit event object. Copy it to the script editor make sure the sheet names are correct and add the checkbox to Sheet1 I1 and if you wish you can use a datavalidation drop down for selecting the games numbers ... it's up to you.

function onEdit(e) {
  e.source.toast('Entry')
  var sh=e.range.getSheet();
  if(sh.getName()=='Sheet1' && e.range.columnStart==9 && e.range.rowStart==1 ) {
    e.source.toast('Flag1');
    e.range.setValue('FALSE');//reset switch
    var hdrToVal={}; //hdr to value from sheet1
    var valueA=sh.getRange(5,2,1,4).getValues()[0];
    var hdrA=sh.getRange(1,2,1,4).getValues()[0];
    hdrA.forEach(function(hdr,i){hdrToVal[hdr]=valueA[i];});
    var sh2=e.source.getSheetByName('Sheet2');
    var game=sh.getRange(1,8).getValue();//game number used to get row on sheet2
    var hA=sh2.getRange(1,3,1,sh2.getLastColumn()).getValues()[0];
    var hdrToCol={};//header to columns
    var hdrToIdx={};//header to vA index
    hA.forEach(function(hdr,i){hdrToCol[hdr]=i+3;hdrToIdx[hdr]=i;});
    var vA=sh2.getRange(3,1,sh2.getLastRow()-2,sh2.getLastColumn()).getValues();
    for(var i=0;i<vA.length;i++) {
      if(vA[i][0]==game) {
        var row=i+3;//got the row now
        break;
      }
    }
    for(var i=0;i<hA.length;i++) {
      if(hdrToVal.hasOwnProperty(hA[i])) {
        sh2.getRange(row,hdrToCol[hA[i]]).setValue(hdrToVal[hA[i]]);//everything comes together here.
      }
    }
  }
}

Sheet1:

Sheet2:

Animation:

Google Sheets - Copy & Paste Data based on 2 things, Game # and Name

1 Answers