Script to copy contents of cell (in a2) and paste this into 1st empty (of text and formulas) cell on row 2

Question

I am trying to write a script to copy contents of cell (in a2) and paste this into the 1st empty (of text and formulas) cell on row 2.

Ones that I have tried to copy and use are to do similar but finding the next row in a column. I have also tried functions that should do this but they find the first cell in the row that contains a formula, I need it to be empty of text and formulas.

Can anyone help please?

Thanks

Answer 1

Copy A2 to end of row2 + 1 col

function copy() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Your Sheet Name");
  const v = sh.getRange("A2").getValue();
  sh.getRange(2, getRowWidth(2,sh,ss) + 1).setValue(v);
}

function getRowWidth(row, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var row = row || sh.getActiveCell().getRow();
  var rcA = [];
  if(sh.getLastColumn()){rcA = sh.getRange(row, 1, 1, sh.getLastColumn()).getValues().flat().reverse();}
  let s = 0;
  for (let i = 0; i < rcA.length; i++) {
    if (rcA[i].toString().length == 0) {
      s++;
    } else {
      break;
    }
  }
  return rcA.length - s;
}

Answer 2

• Use getValues to check whether there's some text in your cells.
• Use getFormulas to check whether there are formulas in your cells.

Code sample:

function copyToFirstEmptyCell() {
  const rowIndex = 2;
  const sheet = SpreadsheetApp.getActiveSheet();
  const row = sheet.getRange(`${rowIndex}:${rowIndex}`);
  const values = row.getValues()[0];
  const a2Value = values.shift();
  const formulas = row.getFormulas()[0];
  const columnIndex = values.findIndex((value,i) => !value.length && !formulas[i+1].length) + 2;
  const foundCell = sheet.getRange(rowIndex, columnIndex);
  foundCell.setValue(a2Value);
  foundCell.activate();
}