CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Curtis Humphreys
Curtis Humphreys

Reputation: 79

Put data in next blank row, selecting wrong column

I'm sure it's simple but i'm trying to get it to paste the data in Column U first blank cell. It's currently pasting in Column A starting from cell A28.

function Update() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getRange("U:U").getValues();
  var maxIndex = values.reduce(function(maxIndex, row, index) {
  return row[0] === "" ? maxIndex : index;
  }, 0);
  sheet.setActiveRange(sheet.getRange(maxIndex + 2, 1));
  sheet.getRange('L28:S').copyTo(sheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

Upvotes: 1

Views: 64

Answers (1)

Tanaike
Tanaike

Reputation: 201613

I believe your goal as follows.

  • You want to put the values to the first blank of the column "U" as the destination range.
  • The source range is L28:S.

Modification points:

  • In your script, the destination range is sheet.getRange(maxIndex + 2, 1). By this, the values are copied to the column "A". I think that this is the reason of your issue.
  • In your situation, I thought that getNextDataCell might be able to be used.

When your script is modified, it becomes as follows.

Modified script:

function Update() {
  var sheet = SpreadsheetApp.getActiveSheet();

  // I modified below script.
  var range = sheet.getRange("U1:U" + sheet.getLastRow());
  var temp = range.getNextDataCell(SpreadsheetApp.Direction.DOWN);
  var destRange = temp.getRow() == sheet.getMaxRows() && sheet.getRange("U1").isBlank() ? sheet.getRange("U1") : temp.offset(1, 0);
  // destRange.activate();  // When you use this line, the start of copy is activated.
  sheet.getRange('L28:S').copyTo(destRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

Reference:

Upvotes: 2

Related Questions