为 Google 电子表格编写脚本有时会很困难,因为采用行号和列号的 Google 电子表格方法使用基于 1 的索引,而 Javascript 数组使用基于 0 的索引。
在这个例子中,单元格 A2
有一个 row == 2
和 column == 1
。 SpreadsheetApp 方法从 A1Notation 中反转列和行,因此这两个范围是等效的:
var range1 = sheet.getRange("A2");
var range2 = sheet.getRange(2, 1);
一旦我将工作表的内容读入数组,情况又不同了。
var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();
var data = sheet.getDataRange().getValues();
之后,我的电子表格中单元格
A2
中的值在 data[1][0]
中。行和列的顺序与 SpreadsheetApp API 相同,但每个都少 1。这里很多问题的答案 (example) 归结为这些不同形式的索引不匹配。充满
row+1
和 col-1
语句的代码很难调试。最后: 如果我知道 A1Notation 中某个单元格的引用,比如
AZ342
,我如何才能在从完整数据范围获得的 data
二维数组中找出与该单元格对应的正确索引值? 最佳答案
显然,您可以非常小心地跟踪您使用任何一种类型的索引的位置,并且您会没事的。
但是做这样的事情会更容易:
var importantCell = "AZ342";
var cellIndexConverted = cellA1ToIndex( importantCell );
var data[cellIndexConverted.row][cellIndexConverted.col] = "Some new value";
转换A1.gs
这里有三个帮助函数来简化从 A1Notation 的转换。
这些辅助函数也是 available as a gist 。
/**
* Convert a cell reference from A1Notation to 0-based indices (for arrays)
* or 1-based indices (for Spreadsheet Service methods).
*
* @param {String} cellA1 Cell reference to be converted.
* @param {Number} index (optional, default 0) Indicate 0 or 1 indexing
*
* @return {object} {row,col}, both 0-based array indices.
*
* @throws Error if invalid parameter
*/
function cellA1ToIndex( cellA1, index ) {
// Ensure index is (default) 0 or 1, no other values accepted.
index = index || 0;
index = (index == 0) ? 0 : 1;
// Use regex match to find column & row references.
// Must start with letters, end with numbers.
// This regex still allows induhviduals to provide illegal strings like "AB.#%123"
var match = cellA1.match(/(^[A-Z]+)|([0-9]+$)/gm);
if (match.length != 2) throw new Error( "Invalid cell reference" );
var colA1 = match[0];
var rowA1 = match[1];
return { row: rowA1ToIndex( rowA1, index ),
col: colA1ToIndex( colA1, index ) };
}
/**
* Return a 0-based array index corresponding to a spreadsheet column
* label, as in A1 notation.
*
* @param {String} colA1 Column label to be converted.
*
* @return {Number} 0-based array index.
* @param {Number} index (optional, default 0) Indicate 0 or 1 indexing
*
* @throws Error if invalid parameter
*/
function colA1ToIndex( colA1, index ) {
if (typeof colA1 !== 'string' || colA1.length > 2)
throw new Error( "Expected column label." );
// Ensure index is (default) 0 or 1, no other values accepted.
index = index || 0;
index = (index == 0) ? 0 : 1;
var A = "A".charCodeAt(0);
var number = colA1.charCodeAt(colA1.length-1) - A;
if (colA1.length == 2) {
number += 26 * (colA1.charCodeAt(0) - A + 1);
}
return number + index;
}
/**
* Return a 0-based array index corresponding to a spreadsheet row
* number, as in A1 notation. Almost pointless, really, but maintains
* symmetry with colA1ToIndex().
*
* @param {Number} rowA1 Row number to be converted.
* @param {Number} index (optional, default 0) Indicate 0 or 1 indexing
*
* @return {Number} 0-based array index.
*/
function rowA1ToIndex( rowA1, index ) {
// Ensure index is (default) 0 or 1, no other values accepted.
index = index || 0;
index = (index == 0) ? 0 : 1;
return rowA1 - 1 + index;
}