如何从Google表格行中获取列的序号索引？ [英] How to get the ordinal index of a column from a Google Sheets row feed?
问题描述
我试图找出最稳健的方式来确定来自行提要而不是单元格Feed的Google工作表中列的序号位置。
例如，我有以下电子表格：
ABCD
+ ---- + --------------------------------------- - +
标题行| 1 |名称|地址|电话|电子邮件|
+ ---- + --------- + ----------- + --------- + -------- - |
| 2 |简| 1001 | 6139023 | w @ gmail |
+ ---- + --------- + ----------- + --------- + -------- - +
| 3 |乔恩| 1102 | 4320909 | x @ gmail |
+ ---- + --------- + ----------- + --------- + -------- - +
| 4 |杰夫| 1203 | 4132323 | y @ gmail |
+ ---- + --------- + ----------- + --------- + -------- - +
| 5 |珍妮| 1304 | 3346044 | z @ gmail |
+ ---- + --------- + ----------- + --------- + -------- - +
在我的代码中，我能够成功从工作表中获取基于行的订阅源通过Google的Sheet's API。我在Feed中为每行返回的对象包含各种属性，其中大部分属于标题行中的值。例如，单行对象如下所示：
{
id：'https：// spreadsheets。 'google.com / ...'
，'app：edited'：'2017-02-24T23：59：56：56.520Z'
，_links：[self：'https：// .. '，编辑：'https：// ...']
，姓名：'Jane'
，地址：'1001'
，电话：'6139023'
，电子邮件：'w @ gmail'
}
我想查找任何给出来自基于行的订阅源的列，但似乎并不明显如何做到这一点。看起来对象的属性根据它们在标题行中的位置排序，但我很难找到属性引用的列号。
我想要列号的原因是我想为特定的列和行获取基于单元格的Feed，但看起来Google的API要求您知道列的序号位置。也就是说，您需要知道基于单元格的提要的最小列和最大列，以及最小行和最大行。现在，我知道我明显可以查看Sheets UI中的列并确定列所在的位置，但是如何以编程方式执行此操作？总而言之，我试图找到一种编程方式来回答一个简单的问题，例如问题：
手机列的序号位置是什么？
这甚至可以通过Google的换行响应？
Sheetsv4中没有这样的功能。您必须手动执行
在这里我正在寻找手机的位置，它是C3
函数findPosition（）{
var arrayBuffer;
var myArr;
var xhr = new XMLHttpRequest（）;
xhr.open（'GET'，'https://sheets.googleapis.com/v4/spreadsheets/'+myspreadsheetId+'/values/'+\"Sheet1!\"+A+\":\"+Z）;
xhr.setRequestHeader（'Authorization'，'Bearer'+ myAccessToken）;
xhr.onload = function（oEvent）{
arrayBuffer = xhr.response; //注意：不是oReq.responseText
console.log（arrayBuffer+ arrayBuffer）;
myArr = JSON.parse（arrayBuffer）; （myArr.values [0] [i] ===
for（var i = 0; i< myArr.values.length; i ++）{
电话）{
console.log（列位置是+（i + 1））; // +1，因为计数从0开始
}
}
};
xhr.send（null）;
}
输出：
可能还有其他方法可以做到这一点。我刚给你看我的版本。
I'm trying to figure out the most robust way to determine the ordinal position of a column in a google sheet from a row feed, not a cell feed.
So, for example, I have the following spreadsheet:
A B C D
+----+-----------------------------------------+
Header Row | 1 | name | address | phone | email |
+----+---------+-----------+---------+---------|
| 2 | Jane | 1001 | 6139023 | w@gmail |
+----+---------+-----------+---------+---------+
| 3 | Jon | 1102 | 4320909 | x@gmail |
+----+---------+-----------+---------+---------+
| 4 | Jeff | 1203 | 4132323 | y@gmail |
+----+---------+-----------+---------+---------+
| 5 | Jenny | 1304 | 3346044 | z@gmail |
+----+---------+-----------+---------+---------+
In my code, I'm able to successfully fetch a row-based feed from the sheet via Google's Sheet's API. The object I get back for each row in the feed includes various properties, most of which are the values in the header row. So for example, a single row object looks something like:
{
id : 'https://spreadsheets.google.com/...'
, 'app:edited' : '2017-02-24T23:59:56:56.520Z'
, _links : [ self: 'https://...', edit: 'https://...']
, name : 'Jane'
, address : '1001'
, phone : '6139023'
, email : 'w@gmail'
}
I want to find the ordinal position of any given column from the row-based feed, but it doesn't seem immediately obvious how to do this. It does seem the object's properties are ordered according to their position in the header row, but I'm hard pressed to find which column number the property refers to.
The reason I want the column number is that I want to get a cells-based feed for a particular column and row, but it appears Google's API requires that you know the ordinal position of the column. That is, you need to know the min-column and max-column, as well as a min-row and max-row for a cells based feed. Now, I know that I can obviously look at the column in the Sheets UI and determine which position the column is in, but how can I do it programatically?
In sum, I'm trying to find a programmatic way to answer a simple question such as question:
"What is the ordinal position of the phone column?"
Is this even possible with Google's row-feed response?
There is no such feature yet in Sheetsv4. You have to manually perform the Basic Reading methods and parse the response and loop through it.
Here I was looking for the position of "phone" which is C3
function findPosition(){
var arrayBuffer;
var myArr;
var xhr = new XMLHttpRequest();
xhr.open('GET', 'https://sheets.googleapis.com/v4/spreadsheets/'+myspreadsheetId+'/values/'+"Sheet1!"+A+":"+Z);
xhr.setRequestHeader('Authorization', 'Bearer ' + myAccessToken);
xhr.onload = function (oEvent) {
arrayBuffer = xhr.response; // Note: not oReq.responseText
console.log("arrayBuffer "+ arrayBuffer);
myArr = JSON.parse(arrayBuffer);
for(var i = 0; i < myArr.values.length; i++){
if(myArr.values[0][i] === "phone"){
console.log("column position is " + (i+1)); // +1 because counting starts at 0
}
}
};
xhr.send(null);
}
Output:
There may be other ways to do this. I just showed you my version.
这篇关于如何从Google表格行中获取列的序号索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
