如何在Google表格中从一个工作表获取(和设置)单元格值到另一个工作表? [英] How to Get (& Set) cell values from one sheet to another in Google Sheets?

问题描述

希望从我的Google表格文件的第一张表格(POTemplate)(用作订单输入表单)的多个单元格中获取值.第22行是表单中的第一行，可从用户那里收集有关订购商品的数据.
我要设置这些值(POHistory)的选项卡将用作所有订单详细信息的运行日志，每个订单详细信息都键入POTemplate选项卡中.PO模板日志中记录的每个条目应包括每个订单的唯一PO编号(位于POTemplate选项卡的N3单元格中).采购订单日期(POTemplate选项卡的单元格N4).我衷心感谢您的帮助.

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;
}

推荐答案

我可以为您提供您所给定的commit()函数的修改.

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.

这篇关于如何在Google表格中从一个工作表获取(和设置)单元格值到另一个工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！