Insert values in the last row of a determined column with app script

Question

I need to copy the values in the last row of a certain column, I was seeing examples like this... Stackoverflow but they have not served me with my script. The problem I have is that in the column A I have five values and in the column B and C I have only one like this ...
A B C
1 1 1
1 - -
1 - -
1 - -
1 - -
and I want to insert values in the column B andC like this...
A B C
1 1 1
1 2 2
1 2 2
1 - -
1 - -
but the problem with my script is that it inserts them like this...
A B C
1 1 1
1 - -
1 - -
1 - -
1 - -
- 2 2

    //This part its not important
    function last(){
    var ss = SpreadsheetApp.openById("ID");
    var ssh = ss.getSheetByName("Name")
    var Range = ssh.getRange("B1:C"); 
    var getVal   = Range.getValues();

    //This part its the important
    var ss = SpreadsheetApp.getActiveSpreadsheet();     
    var ssh = ss.getSheetByName("Sheet 1");
    var lastRow = ssh.getLastRow() + 1;
    var ssRange = ssh.getRange(lastRow , 2, getVal.length, getVal[0].length).setValues(getVal);
}

I need help so that the values are inserted in the correct row.

EDIT

This is the script that I have...

 function last(){
  var ss = SpreadsheetApp.openById("ID");
  var name = ss.getSheetByName("NAMES");
  var lastRow = name.getLastRow();
  var range = name.getRange("A"+(lastRow)+":D"+(lastRow)); 
  var datas   = range.getValues();

  var actSs = SpreadsheetApp.getActiveSpreadsheet();     
  var sheetOne = actSs.getSheetByName("1");
  var ltRo = sheetOne.getLastRow();
  var startRow = null;
  var i = 0;
  while(i < datas.length)
 {
  if(datas[i][0]){
  startRow = i + 2;
  i++;
  }
  if (!i){
  startRow = 1;
  }
  var ssRange = sheetOne.getRange(startRow, 2, datas.length, 
   datas[0].length).setValues(datas);
  }
 }

and this is the Spreadsheet

score 3 · Answer 1 · edited Aug 23 '18 at 00:25

3

this is the problem:

var lastRow = ssh.getLastRow() + 1;

your finding the last row with datas in your spreadsheet which is in your case 6. use getValues() to get the array of data, then parse to find the index where no data is found in column 2.

and do:

 var ssRange = ssh.getRange(foundIndex , 2, getVal.length, getVal[0].length).setValues(getVal);

Example:

function last(){
  var ss = SpreadsheetApp.openById("ID");
  var name = ss.getSheetByName("Name")
  var range = name.getRange("B1:C"); 
  var datas   = range.getValues();

  var actSs = SpreadsheetApp.getActiveSpreadsheet();     
  var sheetOne = actSs.getSheetByName("Sheet 1");
  var startRow = null;
  var i = 0;
  while(i < datas.length)
  {
    if(datas[i][0])
      startRow = i + 2;
    i++;
  }
  if (!i)
    startRow = 1;
  var ssRange = sheetOne.getRange(startRow , 2, datas.length, datas[0].length).setValues(datas);

edited Aug 23 '18 at 00:25

tehhowch

9,645
4
24
42

answered Aug 22 '18 at 19:11

JSmith

4,519
4
29
45

How i declare the variable for `foundIndex`? i tried to use `getValues()` but i can't found the method tu create foundIndex. thanks for helping. =D – Leon K. Aug 22 '18 at 20:02
@LuisAvl I didn't tried the code but try something like this – JSmith Aug 22 '18 at 21:09
I get the next error... `The getRange method can not be found (null, number, number, number).` is marking the variable `startRow` as null but I do not know why. – Leon K. Aug 22 '18 at 21:59
because datas is empty or datas[i][0] is always with datas wait a sec – JSmith Aug 22 '18 at 22:45
just edited the code please try create a test spreadsheet and share it in comment this way I can help you live directly this will be easier thanks in advance – JSmith Aug 22 '18 at 22:53
It almost works, I think, but the problem now is that if I want to keep running the script, the script put the values in the same cell, it's almost as if I put this ...`var ssRange = sheetOne.getRange(2, 2, datas.length, datas[0].length).setValues(datas);`, I was trying to combine the `getLastRow` or changing the values to the variable `startRow ` but I have not succeeded, Thanks for helping me. – Leon K. Aug 23 '18 at 15:39
[Spreadsheet](https://docs.google.com/spreadsheets/d/1O0XcSIMgJdeRZXv0B3Yh8MrrU27iLfvsAYYbfzCk7Eo/edit?usp=sharing) What I need is that I can be executing the script and the values are added in the last available row of that column and i dont know how share the script jeje – Leon K. Aug 23 '18 at 18:27
You have to share the spreadsheet and allow edit access. Then we can see the script editor also. Or we can copy it, as I did. See my comment below. – Ron Kloberdanz Aug 23 '18 at 18:30

score 3 · Answer 2 · answered Aug 22 '18 at 19:59

3

Another method I have used previously from this answer:

var ss = SpreadsheetApp.getActiveSpreadsheet();     
var ssh = ss.getSheetByName("Sheet 1");
    var Avals = ssh.getRange("B1:B").getValues();
    var Alast = Avals.filter(String).length;
    var ssRange = ssh.getRange(Alast +1 , 2, getVal.length, getVal[0].length).setValues(getVal);

answered Aug 22 '18 at 19:59

Ron Kloberdanz

442
3
8

Hello, Thanks for help me. With the script that you just helped me, it gives me a small error, which is that at the time it brings me the values, I do not know why, but he puts them to almost the end of the sheet. – Leon K. Aug 22 '18 at 20:29
Grr. That shouldn't be happening. That was happening to me when I was using `.getLastRow()` on column B, but it stopped when I switched to this method. Make sure you do as @JSmith did in his answer, and change your variable names. You shouldn't have the same variable names (ss, ssh) for different variables in the same function. The function may be confused and getting data from the wrong place. It's possible that is the cause. I removed the first instances of the ss and ssh variables in my code. – Ron Kloberdanz Aug 22 '18 at 22:14
If it works but only if you do not have any value in that column but and no, I do not have the same name for the variables, in the example that I put if because I do it quickly to give me to understand but in the same way I appreciate your help I will keep trying =D – Leon K. Aug 23 '18 at 16:37
Ok. I just looked at the code you had in the sheet you linked for @JSmith. The problem is that you still have `var ltRow = sheetOne.getLastRow();` before the final line. Remove that, and it works fine. – Ron Kloberdanz Aug 23 '18 at 18:29
But the problem that i have with this example is that if I have blank spaces and then values, do not insert them in the right place. I edit the spreadsheet so you can see what I want to say better. Thans again gor helping me – Leon K. Aug 23 '18 at 18:52
Edit this line: `var val = sheetOne.getRange("B1:B").getValues();`. Change `B1` to whatever you need, `B4` in the case of your example. – Ron Kloberdanz Aug 23 '18 at 19:57

Insert values in the last row of a determined column with app script

2 Answers2