Copy -Paste into next available row in selected column

Question

I am trying to write a script that is tied to a button so that when clicked it copies my selected range into the next available row based on contents in column A. I have formulas in column D and currently when ran it pastes into the next available row but I would like it to paste into the next available row based on if column A has data.

Here is what I have so far

function myFunction() {
 var ss = SpreadsheetApp.getActiveSpreadsheet ();
 var source = ss.getRange ("JOB BUILD!A3:E21");
 var destSheet = ss.getSheetByName("JOB IN PROGRESS");
 var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);
 source.copyTo (destRange, {contentsOnly: false}) 
      }

Welcome to [so]. Is there any problem with your code? What is the question? — Rubén, Nov 12 '20 at 17:53
Does this answer your question? [Determining the last row in a single column](https://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column) — Marios, Nov 12 '20 at 18:11
is this all of your code? I don't see a doGet or any other trigger function. Also, where is this button you mentioned? — Baby_Boy, Nov 12 '20 at 18:14
The code works but pastes in the next available row. I would like it to paste in the next available row based on column A and overwrite anything below it. So if column A has data down to row 4 but column D has data down to row 8 id like it to paste in row 5 not row 9 — Bill Jensen, Nov 12 '20 at 18:18
The button is a drawing that I assigned this script to in google sheets. The code is currently working on the click of the button. I just cannot figure out how to make it paste into the proper range. — Bill Jensen, Nov 12 '20 at 18:22
I believe Tanaike has and answer about loading images into a spreadsheet that are used for buttons but I don't remember which one so I'd recommend searching on his answers if you can. Personally, I would rather build the buttons in the sidebar with html — Cooper, Nov 12 '20 at 18:24
[getColumnHeight()](https://stackoverflow.com/a/55344474/7215091) — Cooper, Nov 12 '20 at 18:31

score 0 · Answer 1 · answered Nov 13 '20 at 10:04

If you want to paste the values based on the last available row from a certain column, you will have to find the last row of that column.

In order to get the last row of a certain row, you can use this method from this answer. Assuming you want to paste the values based on column A from the destSheet, you can try the below code:

function myFunction() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sourceSheet = ss.getSheetByName("JOB BUILD");
    var sourceData = sourceSheet.getRange("A3:E21");
    var destSheet = ss.getSheetByName("JOB IN PROGRESS");
    var aRange = destSheet.getRange("A1:A").getValues();
    var colA = aRange.filter(String).length;
    var destRange = destSheet.getRange(colA, "COL_NO", "NO_OF_ROWS", "NO_OF_COLS");
    sourceData.copyTo(destRange, {
        contentsOnly: false
    })
}

Note

Please bear in mind that depending on the exact structure of your data from the two sheets, you will have to adjust the ranges.

Copy -Paste into next available row in selected column

1 Answers1

Note

Reference