samedi 7 septembre 2019

Copy text to first empty cell

I have a permanent list of names in A38:A90 that list of names makes data validation for B5:B36. What I am trying to do is run a script that when I put a note for "Person1" in F5 (Columns C,D, and E have other data) that note populates in B38 because A38 is "Person1" but the next time that I set a note for "Person1" in F20 and I would like that note to be in C38 and so on. I have this part down...However, my issue is that if the first time that I run the script "Person1" does not have a note and the 2nd time I run it "Person1" does have a note then I want to add the note in B38 not C38.

here is an example image https://docs.google.com/document/d/1EjGhcvXJOklP4qOH9XNAwFiO2OwwTWELEgZcJDvTbU4/edit?usp=sharing

function autoload(){
  var ss = SpreadsheetApp.getActiveSheet();
  var meeting = ss.getRange("B5:R36").getValues(); 
  var targetNames = ss.getRange("A38:A100").getValues();
  var writtingRange = ss.getRange("B38:R100").getValues();
  var updatedUser = targetNames;
  var writtingColumn = -1;
  var coolMatrix;

  for(var i = 0; i < meeting.length; i++){ 
    var name = meeting[i][0] 
    for (var j = 0; j < targetNames.length; j++) { 
      if (targetNames[j][0] == name){
        for (var z = 0; z < writtingRange[0].length; z++) { 
          if (writtingRange[j][z] == '' && writtingColumn < 0) { 
            writtingColumn = z; 
            break; 
          }
        }
        writtingRange[j][writtingColumn] = meeting[i][4];
        updatedUser[j] = true; 
      }
    }
  }
  for (var w = 0; w < writtingRange.length; w++){
    if (writtingRange[w][writtingColumn] == ''){
      writtingRange[w][writtingColumn] = ' ';
    }
  }
  ss.getRange("B38:R100").setValues(writtingRange);
}

Any help would be appreciated :) Thank you

Aucun commentaire:

Enregistrer un commentaire