在电子表格中将一系列数值写入单元格范围

Question

使用Google Apps Script，是否有一种方法可以将Google电子表格数组中的值写入范围而无需循环？

我想将每个名称放入单元格A1:A3中的类似以下内容：

function demoWriteFromArray() {    
   var employees=["Adam","Barb","Chris"];    
   ssActive = SpreadsheetApp.getActiveSheet();    
   rgMyRange = ssActive.getRange("A1:A3");    
   rgMyRange.setValue(employees);
} 

以上的问题是，在执行之后，A1:A3 中全部包含 ={"Adam","Barb","Chris"} 并显示为 "Adam"。

- Playing with GAS

Answer 1

Range.setValue()用于在范围中的每个单元格中设置相同的值，而setValues用于将一组值设置到范围中相应的单元格中。请注意，此方法期望一个多维数组，外层数组为行，内层数组为列。因此，在您的情况下，数据应该长这样：

var employees=[["Adam"],["Barb"],["Chris"]];

- Eric Koleda

---

我遇到了错误“范围宽度不正确，应为1而实际为5（第5行，文件“代码”）”，并查看了提供的文档。我意识到我错过了内部数组是行，外部数组是列的细节。这个答案似乎正确地回答了问题。 - mrcni

我认为这是针对结果的一行而不是问题中所述的结果列。 - ken

那么如何将 ["Adam","Barb","Chris"] 自动转换为 [["Adam"],["Barb"],["Chris"]] 呢？ - vstepaniuk

Answer 2

顶部的答案提供了一种写入数组到行的不错、紧凑的方法，但是要写入列则有点难度，但既然这就是问题所在，这里有一个快速的函数来解决它：

function addArrayToSheetColumn(sheet, column, values) {
  const range = [column, "1:", column, values.length].join("");
  const fn = function(v) {
    return [ v ];
  };
  sheet.getRange(range).setValues(values.map(fn));
}

然后您可以在代码中按以下方式调用该函数：

const ss = SpreadsheetApp.getActiveSpreadsheet();
const cache = ss.getSheetByName("_cache_");
const results = ["Adam","Barb","Chris"];
addArrayToSheetColumn(cache, "A", results);

- ken

Answer 3

即使有点晚了，但某些人可能会在某个时候看到这个答案。我写了一个函数，将一维数组强制转换为二维数组（矩阵），这是 Range.setValues() 期望的参数。

// Morphs a 1-d array into a 2-d array for use with Range.setValues([][])
function morphIntoMatrix(array) {

  // Create a new array and set the first row of that array to be the original array
  // This is a sloppy workaround to "morphing" a 1-d array into a 2-d array
  var matrix = new Array();
  matrix[0] = array;

  // "Sanitize" the array by erasing null/"null" values with an empty string ""
  for (var i = 0; i < matrix.length; i ++) {
    for (var j = 0; j < matrix[i].length; j ++) {
      if (matrix[i][j] == null || matrix[i][j] == "null") {
        matrix[i][j] = "";
      }
    }
  }
  return matrix;
}

我不能保证除基本数据类型外的任何值都将被保留。这是因为 JavaScript 在内部似乎会用 null 或 "null" 替换原数组中的空值，当它被赋值为另一个数组的元素时，清理数据 是必要的。至少，这是我的经验。尽管如此，对于某些人可能有用。

- Chris Cirefice

Answer 4

我将脚本执行时间提高了10倍。 它对我来说是这样工作的:

function xabo_api() { 
var sskey = 'KEY-CODE';
var doc = SpreadsheetApp.openById(sskey);    
var conn = Jdbc.getConnection("jdbc:mysql://HOST:PORT/DB", "USER", "PWD");
var stmt = conn.createStatement();

//stmt.setMaxRows(5000);

var rs = stmt.executeQuery("select * from xabo;");
var sheet = doc.setActiveSheet(doc.getSheetByName("xabo laboratories"));

//var cell = doc.getRange('A2');

var row = 0;
var data = [];

while (rs.next()) {
    var rowData = [];
    for (var col = 0; col < rs.getMetaData().getColumnCount(); col++) {
    rowData[col] = (rs.getString(col + 1));
}
data[row] = rowData;
row++;
}

 // My script writes from the second row, as first I use for headers only

 var range = sheet.getRange(2,1,row, col); // check edges or uses a1 notation
 range.setValues(data);

 //   This is what google api doc suggests: It takes 10x more row by row
 //   while (rs.next())
 //   {
 //       for (var col = 0; col < rs.getMetaData().getColumnCount(); col++){
 //         cell.offset(row, col).setValue(rs.getString(col + 1));
 //        }
 //     row++;
 //   } 

 rs.close();
 stmt.close();
 conn.close();
 }

- user2890988

---

这似乎并没有回答楼主的问题。 - Derek

这里存在一个潜在的问题，即当出现错误时似乎没有回滚。也许这就是谷歌推荐不同方法的原因。（我正在查找 Stack Overflow 上的解决方案来解决我的代码问题） - Zach Smith

Answer 5

为了让数组元素能够写入列中，您还可以使用 array.forEach()。

这个：

var employees=["Adam","Barb","Chris"];

employees.forEach((item,index,array) => array[index] = [item]);

将对您的数组进行以下更改：

["Adam","Barb","Chris"]  -->  [["Adam"],["Barb"],["Chris"]]

forEach的更多信息： https://developer.mozilla.org/zh-CN/docs/Web/JavaScript/Reference/Global_Objects/Array/forEach

- kevin_

Answer 6

一个更简单的解决方案是映射整个数组并使用toString()函数进行转换，然后使用。

range.setValue(employees.toString())

- Shiny

Answer 7

在研究自己的应用程序答案时，我看到了这个问题。另一种解决方案是使用返回2D数组的自定义函数。

以下是一个例子。假设"A1"中的文本为"Adam, Barb, Chris; Aaron, Bill, Cindy"。如果您在"B1"中引用自定义函数，则可以将单个名称填充到范围"B1:D2"中。

Apps Script编写的自定义函数：

function splitNames(names) {
  // you could do any other work necessary here
  var arr = names.split("; ");
  arr[0] = arr[0].split(", ");
  arr[1] = arr[1].split(", ");

  // output should be 2D array where each element of the
  // main array is a range spanning 1 or more columns and
  // one row and each sub array are cells within that range
  return arr;
}

B1单元格中引用自定义函数的公式为：=splitNames(A1)

请注意，如果B1相邻的单元格不为空，此函数将抛出异常。可以使用IFERROR公式或在自定义公式中使用try/catch来处理此问题。

- mjblay

Answer 8

var employeesRow = ["Adam","Barb","Chris"];    
var employeesCol = employeesRow.map(x => [x]); //where x is your item/element in the array
Logger.log(employeesCol); //expected result [["Adam"],["Barb"],["Chris"]]

- Stefana Popova

Answer 9

你可以使用 push

var employees=["Adam","Barb","Chris"];
var newarray = [[],[]] ;
newarray[0].push(employees) ;
ssActive = SpreadsheetApp.getActiveSheet();
rgMyRange = ssActive.getRange("A1:A3");
rgMyRange.setValues(newarray)

请注意，您需要将setValue更改为setValues

- cooogeee

Answer 10

这里有四个函数：(1) 设置水平值范围 (2) 设置垂直值范围 (3) 设置数值区域 (4) 复制数值区域并粘贴。

function SetHorizontalRange() {
var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var Sheet = Spreadsheet.getActiveSheet();
var row = 1; var numRows = 1;
var column = 1; var numColumns = 3; //("A1:C1")
var Range = Sheet.getRange(row, column, numRows, numColumns)
var values = [[["Adam"],["Barb"],["Chris"]]];


Range.setValues(values)
} 

//////////////////////////////////////////////////////////////////////////////ℱℳ
function SetVerticalRange() {
  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet = Spreadsheet.getActiveSheet();
  var row = 1; var numRows = 3; 
  var column = 1; var numColumns = 1; //("A1:A3")
  var Range = Sheet.getRange(row, column, numRows, numColumns)
  var values = [["Adam"],["Barb"],["Chris"]];


  Range.setValues(values)
} 

//////////////////////////////////////////////////////////////////////////////ℱℳ
function SetArea(){
  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet = Spreadsheet.getActiveSheet();
  var row = 1; var numRows = 3; 
  var column = 1; var numColumns = 3; //("A1:C3")
  var Range = Sheet.getRange(row, column, numRows, numColumns)
  var values = [[[["Adam"]], [["Barb"]], [["Chris"]]], [[["Barb"]], [["Chris"]], [["Adam"]]], [[["Chris"]], [["Adam"]], [["Barb"]]]];


  Range.setValues(values)
}

//////////////////////////////////////////////////////////////////////////////ℱℳ
function CopyPasteArea(){
  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet = Spreadsheet.getActiveSheet();
  var row = 1;  var column = 1;
  var Range = Sheet.getRange(row, column);
  var values = new Array(3);
  for (var y = 0; y < 3; y++) {
    values[y] = new Array(3);
    for (var x = 0; x < 3; x++) {
      values[y][x] = Range.offset(x,y).getValues(); Logger.log(x+' '+y);      
    }
  }


  Sheet.getRange(4, 1, 3, 3).setValues(values); Logger.log(values)
  //https://developers.google.com/apps-script/best_practices
}

- Frank Montemorano

---

哎呀，为什么要在这里写出如此无用且不可运行的代码作为答案呢？ - Taras

我完全同意其他评论。请考虑将其删除并阅读GAS文档。 - Serge insas

抱歉，我忘记了一个括号。现在它可以工作了。放置它的原因是为了向人们展示如何设置水平和垂直范围以及数组。也许你们可以告诉我哪里出了问题。 - Frank Montemorano

除了将“音符”替换为“括号”外，这与电影《阿玛迪斯》中的对话相同。原文链接：http://www.toomanynotes.com/Amadeus.htm。 - Serge insas

好观点。但是由于我对GAS和Stackoverflow都很新，所以我会继续保持它，只是为了好玩...并向人们展示不应该做什么。 - Frank Montemorano

@Sergeinsas 或许读这些评论就像 George Lopez 中的这段对话，只不过把“Max”替换成我们的用户名：“George Lopez: Sk8erboyz (#4.6)” (2004) Benny: [看见 Max 和其他几个书呆子孩子] 我以前看过这个电影。 [高喊] Benny: 书呆子! 书呆子! 书呆子! - bearacuda13