CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscopy-paste
Geg
Geg

Reputation: 5

How to Copy a Range of Rows Within a Single Column into a New Column from the First Empty Cell

I'm new to script writing and I am trying to learn it as I go, but I've run into a problem that I'm hoping someone will be able to help me with.

I may not be able to explain it very well, especially with regards to my use of technical terms, but I'll give it my best shot.

Essentially, I am aiming to copy a range of cells (B2:B23) of sheet1 into column A in sheet2 from the first available cell. But, because column B in sheet2 has values down to row 450 and I want to start from A4 I can't use getLastRow() as that jumps to A450.

*(B2:B23) is fixed, and I will C&P values into them periodically, but each time, when I run the script I want sheet2 column A to update with the new values from the next empty cell.

I've tried lifting and adapting variants of code I've found on here and elsewhere, but with no joy so far.

I'll list some of the functions I've been trying with and you might be able to correct me on where I've gone wrong.

var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Batting");
var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Batsmen");
var colA = sheetTo.getRange('A:A').getValues();
var fcolA = colA.filter(function (e) {return e[0];});
var firstEmptyRowIncolA = fcolA.indexOf('')+1;


var valuesToCopy = sheetFrom.getRange(2, 2, sheetFrom.getMaxRows(), 1).getValues();
  sheetTo.getRange(firstEmptyRowIncolA+4,1,valuesToCopy.length,1).setValues(valuesToCopy);

}


function outputAppend() {
var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Batting");
var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Batsmen");


var valuesToCopy = sheetFrom.getRange(2, 2, sheetFrom.getMaxRows(), 1).getValues();
sheetTo.getRange(sheetTo.getLastRow()+1,1,valuesToCopy.length,1).setValues(valuesToCopy);
}


function copytestdata() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Batting");
  var sheet2 = ss.getSheetByName("Batsmen");
  var players = sheet1.getRange("B2:B23");
  var row=4;
  var col=1;
  var value= players.getValue();

//  sheet1.getRange("B2:B23").copyTo(sheet2.getRange(sheet2.getLastRow()+1,1,1,1), {contentsOnly:true});
// }

  players.copyTo(sheet2.getRange(row,col)); 
  while(sheet2.getRange(row,col).getValue()!=""){
  row++
  }

//  while(sheet1.getRange(row,col).getValue()!=""){
//    row++;
//  }
//  
//  sheet2.getRange(row,col).setValue(value);
}```

As you can see, it's all a bit messy and I've tied myself up in knots, so any help would be great.
Thanks

Upvotes: 0

Views: 169

Answers (1)

ale13
ale13

Reputation: 6062

In order for your solution to work you should be using the following functions:

The getNextIndexA() is used to find the index of the first empty cell from the Batsmen sheet.

function getNextIndexA() {
  var sheetTo = SpreadsheetApp.getActive().getSheetByName("Batsmen");
  var myValues = sheetTo.getRange("A:A").getValues();
  var i;
  for (i=0; i<myValues.length; i++) {
    if (myValues[i][0] === '')
      return i+1;
  }
  return i+1;
}

The copyData() is used to copy the data you want into the the next free row.

This is done by using the .getRange() with the B2:B23 parameters, the .copyTo method and the .getRange() with the following parameters:

  • row: getIndexA() which returns the first empty cell from A
  • column: 1 which is the column corresponding to A
  • numRows: 22 which is the number of rows that need to be copied
  • colRows: 1 which is the number of columns that need to be copied.
function copyData() {
  var sheetFrom = SpreadsheetApp.getActive().getSheetByName("Batting");
  var sheetTo = SpreadsheetApp.getActive().getSheetByName("Batsmen");
  var valuesToCopy = sheetFrom.getRange("B2:B23").getValues();

  sheetFrom.getRange("B2:B23").copyTo(sheetTo.getRange(getNextIndexA(), 1, 22, 1));
}

Furthermore, I suggest you take a look at the following links since they might be of help:

  1. Class Sheet Apps Script - getRange();

  2. Class Range Apps Script - copyTo().

Upvotes: 1

Related Questions