0

Been struggling with this for days and cannot get it working the way I like.. Basically I want to have a script that takes a value in a specific cell and copies it into the next available row of a specific column in another sheet (tracking daily changes in a stock portfolio). I want to copy today's date into column D and the value from the other sheet into column E.

enter image description here

I've tried getLastRow() but it just adds the value at the very bottom of the column. I've tried using loop solutions from other questions but it always ends up copying to the wrong place or I just don't comprehend it with my limited coding knowledge.

Here is what I have at the moment which is a mess and I'm just stuck:

    function dailyCaptureGains() {
  // Get the daily value
  var stockdata = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dividend Portfolio');
  var value = stockdata.getRange(27,2).getValue();
  Logger.log("Value = " + value)
  debugger

  // Add the date and stock data to the other sheet
  var gains = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Gains');
  var formattedDate = Utilities.formatDate(new Date(), "GMT", "MM/dd/yy");
  Logger.log(formattedDate);
  debugger

  // Find next empty cells to add the data and value
  var dateRange = gains.getRange('D2:D');
  var dateLastRow = dateRange.getLastRow(); // Get last row
  Logger.log("last row = " + dateLastRow);
  var date = dateLastRow + 1;
  
  debugger
};

Open to any and all solutions. If there is a simpler way to do this please let me know.

tpayne2345
  • 21
  • 1
  • 4
    `gains.getRange('D2:D').getValues().filter(String).length + 1` this will give you the row after the last row with content of column D (given that D does not have empty cells in between as it is the case in the screenshot). – Marios Feb 10 '21 at 14:59
  • Sorry in the formula in my previous comment you should add 2 instead of 1 because you start from the second row. – Marios Feb 10 '21 at 15:13
  • Yes! This has me going in the right direction.. thanks! – tpayne2345 Feb 10 '21 at 15:15

1 Answers1

0

There are two options:

  • getLastRow (sheet method)

    Note that there are two getLastRow methods, the one in the Sheet class and the one in the Range class. In your case, you are using the one of the Range class, with a range D2:D, that is, you don't specify the end, so you do not get the desired result.

    var dateLastRow = stockdata.getLastRow()

  • Javascript trick

    If you know the starting point of your range but not the end, you can take that large range and filter out its empty values.

    var dateRange = gains.getRange('D2:D').getValues();
var dateLastRow = dateRange.filter(String).length; // Get last row
fullfine
  • 1,371
  • 1
  • 4
  • 11