copy and paste to next available row in a certain column google sheets

0
votes

I'm attempting to switch from Excel to Google Sheets and I already have a macro for this, but you can't run macros on Google spreadsheet. What I want is to move the Value in Log-in!C5 to another sheet "w1" starting in column A3. The code I have works but instead of moving it in "w1!A3" it was placed on cell A146 since it was the next empty row. Is there a way for the value to be moved in first empty row in Column A? regardless that the other columns are not empty?

Here is my code

function moveValuesOnly() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var source = ss.getRange('Log-in!C5');
 var destSheet = ss.getSheetByName('w1');
 var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);
 source.copyTo (destRange, {contentsOnly: true});
 source.clear ();
}
1
google-apps-scriptgoogle-sheetscopy-paste

1 Answers

0
votes
  • getLastRow() only gets the lastRow of the sheet and not the range.

  • If you only want w1!A3,hardcode it directly: Try Changing from 

    destSheet.getRange(destSheet.getLastRow()+1,1);

    to 

    destSheet.getRange(3,1);


var colA = destSheet.getRange('A:A').getValues();

Code1:

//Filter 2D array to 1D
var fcolA = colA.filter(function (e) {return e[0];});
var firstEmptyRowIncolA = fcolA.indexOf('')+1;

or

Code2:

for(i=0;i<colA.length;i++){
if(colA[i][0] == ''){
var firstEmptyRowIncolA = i+1;
break;
}
}