How to Get (& Set) cell values from one sheet to another in Google Sheets?

Question

Looking to get values from several cells in the first sheet (POTemplate) of my Google Sheet file that serves as an order entry form. Line 22 is the first line in the form that collects data from our user regarding items requested for order.
The tab to which I'd like to set these values (POHistory) will serve as a running log of all order details keyed into the POTemplate tab with each order. Each entry recorded in the PO template log should include each orders' unique PO No. (found in cell N3 of the POTemplate tab) & PO Date (cell N4 of the POTemplate tab). I sincerely appreciate the help.

function submit() {
  var app = SpreadsheetApp;
  var tplSheet = app.getActiveSpreadsheet().getSheetByName("POTemplate");
  var tplFRow = 22, tplLRow = tplSheet.getLastRow();
  var tplRowsNum = tplLRow - tplFRow + 1;
  var tplFCol = 1, tplLCol = 16;
  var tplColsNum = tplLCol - tplFCol + 1;
  var rangeData = tplSheet.getRange(22, 1, tplRowsNum, tplColsNum).getValues();
  var colIndexes = [0, 3, 10, 12, 15];
  var fData = filterByIndexes(rangeData, colIndexes);
  var target = "POHistory";
  var targetSheet = app.getActiveSpreadsheet().getSheetByName(target);
  var tgtRow = targetSheet.getLastRow() + 1;
  var tgtRowsNum = fData.length - tgtRow + 1;
  var tgtCol = 1;
  var tgtColsNum = fData[0].length - 1 + 1;
   targetSheet.getRange(tgtRow, tgtCol, tgtRowsNum, 
tgtColsNum).setValues(fData);
}

function filterByIndexes(twoDArr, indexArr) {
  var fData = [];
  twoDArr = twoDArr.transpose();
  for(var i=0; i<indexArr.length; i++) {
     fData.push(twoDArr[indexArr[i]]);
   }
  return fData.transpose();
}

Array.prototype.transpose = function() {
  var a = this,
      w = a.length ? a.length : 0,
      h = a[0] instanceof Array ? a[0].length : 0;
   if (h === 0 || w === 0) {return [];}
   var i, j, t = [];
  for (i = 0; i < h; i++) {
     t[i] = [];
     for (j = 0; j < w; j++) {
      t[i][j] = a[j][i];
     }
   }
  return t;
}

David Tew David Tew · Accepted Answer · 2018-03-05T21:09:42

I can offer you a modification of the submit() function you have given.

    function process(){
  submit();
  submitDate();
}

function submitDate() {
  var app = SpreadsheetApp;
  var tplSheet = app.getActiveSpreadsheet().getSheetByName("POTemplate");
  var tplFRow = 1, tplLRow = 21;
  var tplRowsNum = tplLRow - tplFRow + 1;
  var tplFCol = 1, tplLCol = 16;
  var tplColsNum = tplLCol - tplFCol + 1;
  var rangeData = tplSheet.getRange(tplFRow, tplFCol, tplLRow, tplColsNum).getValues();
  var colIndexes = [13];
  var fData = filterByIndexes(rangeData, colIndexes);
  var target = "POHistory";
  var targetSheet = app.getActiveSpreadsheet().getSheetByName(target);
  var tgtRow = targetSheet.getLastRow() + 1;
  var tgtRowsNum = fData.length - tgtRow + 1;
  var tgtCol = 1;
  var tgtColsNum = fData[0].length - 1 + 1;
  Logger.log(fData)
  fData=fData.transpose();
  Logger.log(fData)
  targetSheet.getRange(tgtRow-1, 5, fData.length, 
                       fData[0].length).setValues(fData);
}

function submit() {
  var app = SpreadsheetApp;
  var tplSheet = app.getActiveSpreadsheet().getSheetByName("POTemplate");
  var tplFRow = 22, tplLRow = tplSheet.getLastRow();
  var tplRowsNum = tplLRow - tplFRow + 1;
  var tplFCol = 1, tplLCol = 16;
  var tplColsNum = tplLCol - tplFCol + 1;
  var rangeData = tplSheet.getRange(22, 1, tplRowsNum, tplColsNum).getValues();
  var colIndexes = [0, 3, 10, 12, 15];
  var fData = filterByIndexes(rangeData, colIndexes);
  var target = "POHistory";
  var targetSheet = app.getActiveSpreadsheet().getSheetByName(target);
  var tgtRow = targetSheet.getLastRow() + 1;
  var tgtRowsNum = fData.length - tgtRow + 1;
  var tgtCol = 1;
  var tgtColsNum = fData[0].length - 1 + 1;
  targetSheet.getRange(tgtRow, tgtCol, tgtRowsNum, 
                       tgtColsNum).setValues(fData);
}

function filterByIndexes(twoDArr, indexArr) {
  var fData = [];
  twoDArr = twoDArr.transpose();
  for(var i=0; i<indexArr.length; i++) {
    fData.push(twoDArr[indexArr[i]]);
  }
  return fData.transpose();
}

Array.prototype.transpose = function() {
  var a = this,
      w = a.length ? a.length : 0,
      h = a[0] instanceof Array ? a[0].length : 0;
  if (h === 0 || w === 0) {return [];}
  var i, j, t = [];
  for (i = 0; i < h; i++) {
    t[i] = [];
    for (j = 0; j < w; j++) {
      t[i][j] = a[j][i];
    }
  }
  return t;
}

Basically, similar code runs twice. I could be criticized for doing this, but perhaps it is convenient for you this way.

How to Get (& Set) cell values from one sheet to another in Google Sheets?

1 Answers