0

Im having trouble figuring out what to change in my script to log data on the last row of a specific column. I have two different scripts to log data at different times, but right now they will leave gaps since its identifying the last row of content throughout all columns. Is this a simple change with my script to remove those gaps in the rows?

 var sss = SpreadsheetApp.openById('sampleID');
 var ss = sss.getSheetByName('Forecast data'); 
 var range = ss.getRange('B126');

const now = new Date();
const data = range.getValues().map(row => row.concat(now));

 var tss = SpreadsheetApp.openById('sampleID2');
 var ts = tss.getSheetByName('Archived Data'); 

 ts.getRange(ts.getLastRow()+1, 5,1,2).setValues(data); 

}

enter image description here

Brandon
  • 117
  • 8
  • 2
    maybe this will help you [Determining the last row in a single column](https://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column) – Sergey May 17 '21 at 16:54
  • Its the right direction, but I dont know how to implement the answers into my script – Brandon May 17 '21 at 17:50

2 Answers2

1

Try this:

function myfunc101() {
  const ss = SpreadsheetApp.openById('ID1');
  const sh = ss.getSheetByName('Data to copy');
  const data = [[sh.getRange('B126').getValue(),new Date()]];
  const tss = SpreadsheetApp.openById('ID2');
  const ts = tss.getSheetByName('Archived Data');
  ts.getRange(getColumnHeight() + 1, 5, data.length, data[0].length).setValues(data);
}

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.openById('ID2');
  var sh = sh || ss.getSheetByName('Archived Data');
  var col = col || 5;
  const rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse()
  let s = 0;
  for (let i = 0; i < rcA.length; i++) {
    if (rcA[i].toString().length == 0) {
      s++;
    } else {
      break;
    }
  }
  return rcA.length - s;
}

I did this simple test and I find that it put it's at the first empty row at the bottom of column5

function myfunc101() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const data = [[sh.getRange('A1').getValue(),new Date()]];
  sh.getRange(getColumnHeight(5,sh,ss) + 1, 5, 1, 2).setValues(data);
}

It's different than your example because I used the same sheet as the source and target for simplicity but it forced me to see the error.

Brandon
  • 117
  • 8
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • " I'll assume you know what your doing here." I in fact dont know what im doing here. Im new to javascript. Is there a more efficient way? – Brandon May 17 '21 at 17:16
  • However, I tried utilizing what you provided and its not placing the data in the column. Is there anything I am supposed to change in the second function? – Brandon May 17 '21 at 17:23
  • Its even causing my separate script to assign data to the same sheet to not work as well. – Brandon May 17 '21 at 17:41
  • 1
    I'll setup some data and take a look at it – Cooper May 17 '21 at 18:42
  • 1
    Yes, you were correct I needed to set the getColumnHeight function to work in the targetsheet by changing the second and third parameters. – Cooper May 17 '21 at 19:01
  • Will it still work if I have it openbyID? I wont always be at the computer with the sheets open, so it would ideally need to pull data from closed sheets. – Brandon May 17 '21 at 19:04
  • I think so. Try it. – Cooper May 17 '21 at 19:05
  • Its inputing data, but instead of the 2.33 in the image i posted its putting the word "Range" in the cell. Im assuming its because I havent changed the openbyid yet in the second function, so its pulling a null value? – Brandon May 17 '21 at 19:15
  • Its back to not placing anything in the tables. Ill submit an answer below if you dont mind taking a look. – Brandon May 17 '21 at 19:27
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/232532/discussion-between-brandon-and-metaman). – Brandon May 17 '21 at 20:06
0
function test() {
  const ss = SpreadsheetApp.openById('SampleID');
  const sh = ss.getSheetByName('data to collect');
  const data = [[sh.getRange('B126'),new Date()]];
  const tss = SpreadsheetApp.openById('sampleID2');
  const ts = tss.getSheetByName('Archived Data');
  ts.getRange(getColumnHeight(5,ts,tss) + 1, 5,1, 2).setValues(data);
}

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.openById('sampleID2');
  var sh = sh || ss.getSheetByName('Archived Data');
  var col = col || sh.getRange('B126').getColumn();
  const rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse()
  let s = 0;
  for (let i = 0; i < rcA.length; i++) {
    if (rcA[i].toString().length == 0) {
      s++;
    } else {
      break;
    }
  }
  return rcA.length - s;
}
Brandon
  • 117
  • 8
  • Its executing with no errors, however, there is nothing populating in the cells. – Brandon May 17 '21 at 19:28
  • 1
    I didn't realize that you had changed the defaults in the getColumnHeight() function and the target sheet in the getColumnHeight() function wasn't that same as in the other function. So now the getColumnHeight() is setup so that it needs no parameters. – Cooper May 17 '21 at 19:55
  • Works wonderfully now. Changed the ID names to help clear up where the data is taken from and stored, thank you! – Brandon May 17 '21 at 20:02
  • I have found the getColumnHeight() to be a reliable way to get column height. A lot of people use .filter(e=>e!='') however the problem with that is that it also eliminates zero's and empty cells within the data not just the ones on the end. – Cooper May 17 '21 at 20:06
  • Yeah I can use this for a lot of things now. Would you mind editing the IDs out and replacing them with what I put in my answer? Ill mark yours as correct, but I didnt mean to put the ids in there. It wont let me do it. – Brandon May 17 '21 at 20:10