Export / save data to next available row within column-CodePudding

I'm looking to save data from a particular cell to another sheet. On said sheet I need it to save to the next available row within a defined column. I have the script below that will save the data to the next available row, but am unsure of how to define the column. In the example below, I'd need it to be saved into the 'A' column.

function transferList() {

  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cancellations");
  var sourceData = sourceSheet.getRange("D5").getValues();
  var targetSS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet12");
  var targetRangeTop = targetSS.getLastRow();
  targetSS.getRange(targetRangeTop 1,1,sourceData.length,sourceData[0].length).setValues(sourceData);

}

CodePudding user response：

Issue:

If I understand you correctly, you want to write this to the first empty cell of a specific column (which may not correspond to the first empty row in the sheet).

Solution:

I'd suggest using Range.getNextDataCell to find the last cell before the first empty one, and Range.offset to get that empty one.

Code sample:

function transferList() {
  var columnIndex = 1; // Column A
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("Cancellations");
  var sourceData = sourceSheet.getRange("D5").getValue();
  var targetSS = ss.getSheetByName("Sheet12");
  var a1Range = targetSS.getRange(1,columnIndex);
  var targetRange;
  if (a1Range.offset(1,0).getValue() !== "") targetRange = a1Range.getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0);
  else targetRange = targetSS.getRange(2,columnIndex);
  targetRange.setValue(sourceData);
}

Note:

Since you are getting a value from a single cell, there's no need to use getValues and setValues; you can use getValue and setValue instead.