Copy to first empty cell of a specific row-CodePudding

I am attempting to create a script that will copy the value of my source cell (‘Save’!B2) to a new target cell. I want the target cell to always be the first blank cell of the active row. (The active row is defined by ‘Save’!B1)

Sadly, my current formula is copying the cell information to the last column of the array 1.

This is my formula so far:

function saveData() { 
const ss = SpreadsheetApp.getActive(); 
const saveSheet = ss.getSheetByName('Save'); 
const saveRow = saveSheet.getRange('B1').getValue(); 
const dataSheet = ss.getSheetByName('Data'); 
const names = dataSheet.getRange('A1:A').getValues().flat(); 
const targetIndex = names.indexOf(saveRow); 

if (targetIndex === -1) { ss.toast(Name '${saveRow}' cannot be found.); return; } 

  dataSheet.getRange(targetIndex   1, dataSheet.getLastColumn()   1)
 .setValue(saveSheet.getRange('B2').getValue()); ss.toast(Data saved.); }

Sample worksheet: https://docs.google.com/spreadsheets/d/1wutGF_PIXNnISzCkCvXm5OkzP1cvVeTxfZ3_eVILkgY/edit

So far I've tried using getLastColumn and getNextDataCell.Direction.NEXT

Edit to add in some screenshots to elaborate what I am looking for.

CodePudding user response：

You should get all the values of the row selected, get the length of it and then add the new values. Your script modified:

function saveData() {
  const ss = SpreadsheetApp.getActive();
  const saveSheet = ss.getSheetByName('Save');
  const save = saveSheet.getRange('B1').getValue();
  const dataSheet = ss.getSheetByName('Data');
  const names = dataSheet.getRange('a:a').getValues().flat();
  const targetIndex = names.indexOf(save);

  if (targetIndex === -1) {
    ss.toast(`Name '${save}' cannot be found.`);
    return;
  }
  //get values of selected row and its length
  const selectedRow = dataSheet.getRange(targetIndex   1, 1, 1, dataSheet.getLastColumn()).getValues().flat();
  const lastColumnSelectedRow = selectedRow.filter(String).length
  
  dataSheet.getRange(targetIndex   1, lastColumnSelectedRow 1).setValue(saveSheet.getRange('B2').getValue());
  ss.toast(`Data saved.`);
}

CodePudding user response：

Try it this way:

function saveData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Save');
  const saveRow = sh.getRange('B1').getValue();
  const dataSheet = ss.getSheetByName('Data');
  const names = dataSheet.getRange('A1:A'   sh.gerLastRow()).getValues().flat();
  const targetIndex = names.indexOf(saveRow);
  if (targetIndex === -1) {
    ss.toast(`Name ${saveRow} cannot be found.`);
    return;
  }
  dataSheet.getRange(targetIndex   1, dataSheet.getLastColumn()   1)
    .setValue(sh.getRange('B2').getValue());
  ss.toast('Data saved.');
}