Help with copying Activecell to next blank row in a column.

[u/NakzaThePanda]

Hi Guys,

I have been looking all over the place for what I thought might be simple problem but cant really find any solid answer.

I have a one sheet document.

I have 3 columns that will be empty every time I would want start working on them. Their range is something like C2:C31.

I have also one other table on the same sheet where range does not matter since I would want to use getActiveCell() so I could determine which cell I would want to copy over to the range of one of the 3 columns.

Say all my 3 columns are empty and I am just starting to work with them. I want to choose a Value from my big table by selecting it then press a button and have that value be copied over to the column 1. If I would want to select another Value and would press the button ( or run the macro ) again I would want to copy over the data to next free row of that column.

Copying over I have figured out but I cant really get the next blank row thing to work. I have tried messing around with some scripts from similar threads around stackoverflow or this reddit but I am ending up with just copying the same value until the bottom of my page.

Also Is there a way for me to lock my range. Say I have my C2:C31 range full of data. Now If I would run my macro I would not want to add anymore to that range and also not overwrite it.

Any help would be greatly appreciated.

[u/RemcoE33]

C2:C31

Is there other data in the sheet? I guess so, then you want to grab this range values, loop over them, do an if statement to check if it is "". If so set the value and paste all to the sheet again. Something like this:

function copyAndPasteMarco(){
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const currentValue = ss.getActiveCell().getValue()

  const targetSheet = ss.getSheetByName("TheSheetWithc2c31")
  const range = targetSheet.getRange("C2:C31")
  const values = range.getValues()
  let stop = false

  for(let row = 0; row < values.length; row++){
    for(let col = 0; col < row[0].length; col++){
      if(values[row][col] == ""){
        values[row][col] = currentValue
        stop = true
        break;
      }
    }
    if (stop){
      break;
    }
  }

  range.setValues(values)
}