Seem to be getting an error when trying to copy and paste data from last row of the source google sheet to the target sheet last row

Question

Context

I have been using Google Finance to get my stock market data but sometimes it lags and does not load. So I decided to open up a new tab, and automatically copy and paste the data from the Google Finance tab to the new tab and then use the new tab as my stock data.

I wrote the below code to get the last row of the source sheet of every column to copy and paste it to the last row of the target sheet of every column. Each column has different dimensions (not all stock data starts from the same date so some columns have more data than others).

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName('Live data');
  const targetSheet = ss.getSheetByName('Values (Live Data copied)');
  
  // Data from the last row and column of the source sheet 
  let lastRowS = sourceSheet.getLastRow();
  let lastColS = sourceSheet.getLastColumn();

  // Data in the last row and column of the target sheet 
  let lastRowT = targetSheet.getLastRow() + 1;
  let lastColT = targetSheet.getLastColumn();

// For every column in the target sheet, we want to copy and paste the values in the last row from every column in the source sheet. 
for(var i = 1; i <= lastColT; i++){
    // Get data in the last row of the source sheet and paste it into the target sheet. 
    sourceSheet.getRange(lastRowS, lastColS).setValue(lastRowT, i);

  }


}

Problem

How do I copy and paste the data from the last row of every column or where there is data in the source sheet to the last row of every column in the target sheet?

** Update

This is the sheet

[Determining the last row in a single column](https://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column) — Marios, Dec 28 '20 at 15:57

CMB · Accepted Answer · 2020-12-28T20:28:20.850

2

Building on the trick from Determining the last row in a single column, you can filter the range to determine the last row of each column that has value for the source sheet:

Sample Code:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName('Live data');
  const targetSheet = ss.getSheetByName('Values (Live Data copied)');
  
  // Last row and column of the source sheet 
  let lastRowS = sourceSheet.getLastRow();
  let lastColS = sourceSheet.getLastColumn();

  //  Last row of the target sheet 
  let lastRowT = targetSheet.getLastRow();

// For every column in the source sheet, we want to copy and paste the values in the last row from every column in the source sheet. 
  for (var i = 1; i <= lastColS; i++) { 
    var lastRowSrcNew = sourceSheet.getRange(3,i,lastRowS).getValues().filter(String).length; // start from Row 3
    var lastRowTgtNew = targetSheet.getRange(1,i,lastRowT).getValues().filter(String).length;
    var lastVal = sourceSheet.getRange(lastRowSrcNew+2,i).getValue();
    targetSheet.getRange(lastRowTgtNew+1,i).setValue(lastVal);
  }
}

Sample Results:

edited Dec 28 '20 at 20:28

answered Dec 28 '20 at 16:32

CMB

4,950
1
4
16

Thank you so much for this! It works but it seems to be getting data for just one column every two columns. It misses one then gets the other. So I have 8 columns, it just got the data for four. Also, it gets the data from the row before the last row. – Draco D Dec 28 '20 at 16:55
Are there any other lines that increments ````i```` before looping? I ran the script again on my machine using 8 columns and it gets data for all of them. – CMB Dec 28 '20 at 17:23
No there is not. I just ran it 8 times, this is what it does. 1. It gets the row before the last row, for the first column and second column, copies it to the new sheet. The first column it copies it to the cell with no value and the column next to it, it copies the value one cell up. I have included a picture in the original post. It does this for all columns. So it does copy. I had to delete a few rows to see this. – Draco D Dec 28 '20 at 17:53
I see, thanks. So you wanted to copy the last row of every column in the source sheet to a single row in the target sheet? I edited my answer as well. – CMB Dec 28 '20 at 18:08
Thanks, this works but still has some issues. Perhaps I should deliver a sample of the sheet to get an idea of how it looks. I have attached the sheet to give an idea of what it looks like – Draco D Dec 28 '20 at 18:39
1

Your actual data starts at Row 3, so I edited the code to filter the range starting from Row 3. Also, your 12/22 and 12/23 entries do not tally across sheets. Please copy those rows manually first to reconcile before running the script for 12/24. – CMB Dec 28 '20 at 19:15
Okay, so firstly just wanted to thank you so much for your help. This is your time and I'm grateful that you are using it to help me out here. I recopied everything and deleted the last rows for each column (24/12). I then ran it and it copied the date column, but in the close column, the value for 24/12 goes one cell up instead of aligning with it. I fiddled with the positions of the paste target rows but it went further up. The one time it went to its right cell was when I changed the `+1` to `+2` in this `targetSheet.getRange(lastRowTgtNew+1,i).setValue(lastVal);`. But both went down by 1 – Draco D Dec 28 '20 at 19:46
That's because in the target sheet, the 12/23 close column has no value, thus the 12/24 entry is placed there. But in the source sheet everything is aligned, so there is data missing on the target sheet, which is 12/22. The date and close columns in the target sheet need to be aligned as well before running the script. – CMB Dec 28 '20 at 20:01
I read your old comment so I copied and pasted everything from the source to the target sheet anew manually then deleted the values and date for 24/12. That's what I was saying in my previous comment. That way if there were any values missing then this would solve it. I deleted the last rows in the target sheet to test the code. – Draco D Dec 28 '20 at 20:04
Thank you very much! No need to worry, this is more than helpful. :D – Draco D Dec 28 '20 at 20:27
1

Much appreciated :) – CMB Dec 28 '20 at 20:29

Draco D · Answer 2 · 2020-12-31T01:14:56.213

With help from guys at the Google Community, this seemed to work for me. Though the above was also feasible.

function copyData() {
  SpreadsheetApp.flush();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName('Live data');
  const targetSheet = ss.getSheetByName('Live Data copied');
  const data = sourceSheet.getDataRange().getDisplayValues();
  targetSheet.getDataRange().clearContent();
  targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

Seem to be getting an error when trying to copy and paste data from last row of the source google sheet to the target sheet last row

2 Answers2