first empty cell in a row.

[Jason White]

I am brand new to apps script and have been using Gemini to write code for me to help me automate tasks in google sheets. I have been able to get almost everything to work until now. I am trying have a script that will take data from one sheet and copy into another sheet. I only need 5 cells copied but I started with 1 cell to keep it simple, some of the cells I need copied have formulas in them but I only need value copied.. The problem seems to be an empty cell issue. So here is the latest code that gemini created. 

function copyEOWDataToAveragePay() {

  // Source sheet and cell (replace with your sheet names and cell reference)

  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EOW Sheet");

  const sourceCell = sourceSheet.getRange("A2");

  // Target sheet and row (replace with your sheet name and row number)

  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("AVERAGE PAY");

  const targetRow = 2;

  // Get the value from the source cell

  const sourceValue = sourceCell.getValue();

  // Function to find the first empty cell in a row (based on values)

  function findFirstEmptyCell(row) {

    if (!row) {  // Check if row is undefined or null

      return -1;

    }

    const values = row.getValues()[0];

    const emptyCellIndex = values.findIndex(value => !value.length);

    return emptyCellIndex > -1 ? emptyCellIndex + 2 : -1;  // Return column index (1-based) or -1 if not found

  }

  // Find the first empty cell in the target row

  const emptyCellCol = findFirstEmptyCell(targetSheet.getRange(targetRow, 1, 1, targetSheet.getLastColumn()));

  if (emptyCellCol > 0) {

    // Get the target cell range based on the empty cell column

    const targetCell = targetSheet.getRange(targetRow, emptyCellCol);

   

    // Copy the value to the target cell

    targetCell.setValue(sourceValue);

    console.log("Data copied from", sourceCell.getA1Notation(), "to", targetCell.getA1Notation());

  } else {

    console.log("No empty cell found in row", targetRow, "of", targetSheet.getName());

  }

}

Thanks for any help you can give.

Jason

[Jason White]

Does anyone have any ideas?

[Marco]

Olá, 

Dá para resolver com duas linhas, se for somente 5 células consecutivas: 

var values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Página1').getRange('A1:D1').getValues();

SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Página2').getRange('A1:D1').setValues(values);

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c49d0400-36bd-475e-8746-6047e5902ddcn%40googlegroups.com.

PRESERVE A NATUREZA, EVITE O DESPERDÍCIO DE PAPEL E PENSE ANTES DE IMPRIMIR.

"Responsabilidade com o MEIO AMBIENTE"

Esta mensagem e quaisquer arquivos em anexo podem conter informações confidenciais e/ou privilegiadas. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, por favor não leia, copie, repasse, imprima, guarde, nem tome qualquer ação baseada nestas informações. Por favor notifique o remetente imediatamente por e-mail e apague a mensagem permanentemente. Este ambiente está sendo monitorado para evitar o uso indevido de nossos sistemas.

[Edward Wu]

Here's one way (though there may be better ways, depending on a few factors):

Change this:

  const emptyCellCol = findFirstEmptyCell(targetSheet.getRange(targetRow, 1, 1, targetSheet.getLastColumn()));

to this:
  const emptyCellCol = targetSheet.getLastColumn()+1;

 And get rid of this entire section:

  function findFirstEmptyCell(row) {

    if (!row) {  // Check if row is undefined or null

      return -1;

    }

    const values = row.getValues()[0];

    const emptyCellIndex = values.findIndex(value => !value.length);

    return emptyCellIndex > -1 ? emptyCellIndex + 2 : -1;  // Return column index (1-based) or -1 if not found

  }

Reason:

In the AVERAGE PAY tab, say you have these values in Col A, Col B, and Col C:

"Something", "another something", "a 3rd something"

...so the next empty Column would be Col D.

The findFirstEmptyCell function will grab your 3 values, and return the fact that there are "no empty columns" (since the getLastColumn() part will grab the columns with data).

You can visualize this better by adding Logger.log(values); after const values = row.getValues()[0];

...and looking at the data that grabs.

Using Logger.log while you're learning can be very helpful.

Instead, you can jump straight ahead to just getting the last column (with data), and add 1 to it (i.e. the *next* available column).

--

[Timothy Hanson]

I have found that of these three AIs, Copilot, chatGPT, and Gemini, Gemini is the worst, and Copilot is the best (by a lot for waiting code snippets). I was surprised by this; I thought it would be the opposite.

Try Copilot to see if it gets you closer to what you want. Or even better try all three and compare their output, this can give lots of insight into code writing

[Andrew Apell]

Copilot is definitely the best.

To make it even better, always ask for sources, ask it if it is really sure about its solution and, of course, test your code snippet first before using it.

[Saji Debongnie]

I actually made a script that works for what you're trying to do (also new to apps script) and for some reason it works only for one of my sheets, and not the other ones so I'm still trying to figure out what's the issue there but maybe it works just fine for all your sheets and your quest might come to an end ^^ Simply note that you need to address in to which column you want to paste to the second sheet and what's the range of the copied cells in your first sheet.

I am indeed using Logger.log and some variables, and there might be better ways but I tried this and it worked so I'd say I'll stick to it... you need to first mention your variables and  Logger.log outside of your function (whether it's onEdit(e) or not. You can copy paste this if you want :

var pastesheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Name of the sheet you want to paste to');

var columnX = pastesheet.getRange("Range of the column(s) you want to paste to. e.g. A:A").getValues();

var firstemptycell = null; // Declare firstemptycell variable

for (var j = 0; j < columnX.length; j++) { // j can be any other letter, just make sure it's the only use for it

if (!columnX[j][0]) { // Check if the cell is empty

firstemptycell = pastesheet.getRange(j + 1, 1);

break; // Exit loop once the first empty cell is found

}

}

var emptyrange = firstemptycell; // Assign firstemptycell to emptyrange, not sure I needed this bit but it worked

// You can now use the emptyrange variable for further operations

Logger.log("Empty range: " + emptyrange.getA1Notation());

This will actually iterate through your range until it finds an empty cell but this doesn't mean it will select it... so we still need a bit of code to do so.

If you want to upgrade your function to make it work onEdit(e), I recommend constants such as the following to make your script clearer :

function onEdit(e) {

const sheet = e.source.getActiveSheet();

const editedRange = e.range;

Now let's get to the range you want to copy :

if (editedRange.getColumn() === 1 && editedRange.getRow() > 1) { // this is just for me because I want it to automate the edited range whenever I edit (paste actually) column A in a row > 1

const numRows = sheet.getLastRow() - editedRange.getRow() + 1; // this constant is here to determine your range to copy starting from edited column to the last one you want to include

const targetRange = sheet.getRange(editedRange.getRow(), 1, numRows, 11); // Here, "Row 1" of edit -> "Row 11" from edit = Cell 1 of edited row/range -> Cell 11 of edited range. It's actually Column A -> to Column K if editing column A, if you just need fix columns then simply use

For the last line above, if you just need fix columns (not from an edited column/cell like me), I suggest you use getColumn() instead. If you want to copy paste from one sheet to another when pasting in the first sheet, then you'll want to use something like what's above. Otherwise, this will suffice :

const targetRange = sheet.getRange(getColumn(), 1, getColumn, 11); // i.e. column A to K

Finally, the following line is your ultimate requirement for this to work. In your script, you now need to ask the first set of commands to select  the empty cell in order to paste in the appropriate cell :

targetRange.copyTo(spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Name of the sheet you want to paste to'), true).setActiveSelection(emptyrange), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

}

The reason why you need to go through all this is that you need to open the sheet first (setActiveSheet) before it can search the first empty cell (var firstemptycell), select it (setActiveSelection(emptyrange)) and then paste it (SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false), but all this has to happen after it has copied data in your active sheet (targetRange.copyTo()).

This might no be the most elegant script (again I'm new to apps script), but I'd say it's short and clear enough for what you intend to do.

Hope this works out for you, just like it worked for me though I still need to figure why some lines of my script can prevent this from happening...

[Saji Debongnie]

For the target range, you can also simply use a normal cell range. e.g. from A5 to E5 (you can also choose 5 cells in the same column, of course ->  getRange('A5:A9') ) :

const targetRange = sheet.getRange('A5:E5');

[Saji Debongnie]

UPDATE : First I forgot to mention that you can also use an active range :

const targetRange = sheet.getActiveRange()

But more importantly, I've just found out why it wasn't working for all of my sheets (still don't understand why it only works this way, but found a workaround). You actually first need to ask your active sheet to copy paste something in the same active sheet... 

How I did it :

if (editedRange.getColumn() === 1 && editedRange.getRow() > 1) {

var spreadsheet = SpreadsheetApp.getActive();

spreadsheet.getRange('L1:L1').activate();

spreadsheet.getRange('L2:L2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

};

I simply added a range I am not using in my active sheet so it first copies and pastes empty cells and then copies the edited data (target range), opens the destination sheet, finds and selects the first empty cell within given range (i.e. column A for me) and then pastes all edited cells within target range. There you go ! Fully functional code, now.