CODEWITHSUNDEEP
google-apps-script
Brandon
Brandon

Reputation: 117

What to change in my function to change the range it will archive?

I was able to get the script to log a single cell. However, I am looking now to log 2 columns with 49 rows and then add the date timestamp to the third column. What would I need to change within the script? I have been changing the numerical values and no matter what, its still looking for 1 cell. Is it a greater change than I thought?

function DHLtracking() {
  const ss = SpreadsheetApp.openById('Sample_ID_1');
  const sh = ss.getSheetByName('DHL Shipping Data');
  const data = [[sh.getRange('C3:D51').getValue(),new Date()]];
  const tss = SpreadsheetApp.openById('Sample_ID_1');
  const ts = tss.getSheetByName('Archived Data');
  ts.getRange(getColumnHeight() + 1, 8, data.length, data[0].length).setValues(data);
}

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

Upvotes: 0

Views: 23

Answers (1)

Cooper
Cooper

Reputation: 64072

Try this:

function DHLtracking() {
  const ss = SpreadsheetApp.openById('Sample_ID_1');
  const sh = ss.getSheetByName('DHL Shipping Data');
  const sr = 3;
  const sc = 3;
  const dt = new Date();
  const data = sh.getRange(sr, sc, 49, 3).getValues().map(r => r[2] = dt)
  const tss = SpreadsheetApp.openById('Sample_ID_1');
  const tsh = tss.getSheetByName('Archived Data');
  tsh.getRange(tsh.getLastRow() + 1, 8, data.length, data[0].length).setValues(data);
}

Upvotes: 1

Related Questions