0

My script was previously working with no issues, populating data once a day on the next row of its column (5). However, it stopped working. Now it will only work on a blank sheet with no previous data. The first two columns contain the same script though and are still working. Is this just a random bug? When it executes, it claims it did so successfully

function test() {
  const ss = SpreadsheetApp.openById('sourceID');
  const sh = ss.getSheetByName('forecast');
  const data = [[sh.getRange('B126').getValue(),new Date()]];
  const tss = SpreadsheetApp.openById('archiveID');
  const ts = tss.getSheetByName('Archive');
  ts.getRange(getColumnHeight() + 1, 5, data.length, data[0].length).setValues(data);
}

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.openById('archiveID');
  var sh = sh || ss.getSheetByName('Archive');
  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;
}

enter image description here

Brandon
  • 117
  • 8
  • https://stackoverflow.com/questions/67572106/how-to-log-data-into-the-last-row-of-the-specified-column-not-the-last-row-of-a/67575917?noredirect=1#comment119443981_67575917 – Brandon May 21 '21 at 18:09

1 Answers1

0

Try it this way:

function test() {
  const ss = SpreadsheetApp.openById('ssid');
  const sh = ss.getSheetByName('forecast');
  const data = [[sh.getRange('B126').getValue(), new Date()]];
  const tss = SpreadsheetApp.openById('Archive Id');
  const ts = tss.getSheetByName('Archive');
  ts.getRange(getMyColumnHeight(5, ts, tss) + 1, 5, data.length, data[0].length).setValues(data);
}

function getMyColumnHeight(col, sh, ss) {
  if (col && sh && ss) {
    var rcA = [];
    if (sh.getLastRow()) { 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;
  } else {
    throw "Invalid number of parameters";
  }
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • its still not placing anything in the row its supposed to – Brandon May 21 '21 at 18:48
  • If I make a new sheet, and change the paramaters to match that sheets name, it does work. Just not on this sheet anymore, or if the other script is placing data in that one also. – Brandon May 21 '21 at 18:49
  • Maybe my two scripts are in conflict with each other? I am using the original one on the same project directed to the same sheet. Like I said, If im using two different sheets for destinations to log the data, they will work. They just dont work anymore when im sending the data to the same sheet. – Brandon May 21 '21 at 18:59
  • I changed the name of getMyColumnHeight script – Cooper May 21 '21 at 19:01
  • They are all unique. I guess ill just have to direct them to two separate sheets. I think google sheets is having technical issues. It was working perfectly before today. – Brandon May 21 '21 at 19:07
  • i found out specifically, if you change the destination sheet to a new one, and then change it back, it will no longer work. You will then have to send it to a new one from now on. I change the other scripts destination to another one, then sent it back to the original, and that one no longer works anymore either. – Brandon May 21 '21 at 19:11
  • I changed the destination sheet and changed it back and it makes no difference it just keeps on working. You must some other issue at play here. – Cooper May 21 '21 at 19:16
  • I didn't have the first line on test `openById()` until just now perhaps that was the problem. – Cooper May 21 '21 at 19:22
  • I do, I restarted app and put them in a new project and it works. Thanks for the help though – Brandon May 21 '21 at 19:22