Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-macros

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


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.


Solution

  • 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:

    enter image description here

    Sheet2:

    enter image description here

    Animation:

    enter image description here