我在Google工作表中有以下数据，并且尝试获取数组值5与昨天的日期匹配的每一行（N）的更新。 （请注意，我必须先将csv数据粘贴到此处，但该数据位于顶部冻结的Excel工作表中）
运行前的数据
idRef|fName |lName|tourType|dateBooked|tourDate |Pax|phone |pickupAddress|Op|emailaddress |bookedSent|reminderSent|feedbackSent
1 |Josh |w |group |2019-02-12|2019-11-09|3 |821467371|test address |AS|test@mydomain.com|notSent |0 |0
12 |jess |s |group |2019-02-22|2019-11-10|2 |1233333 |test address |as|test@mydomain.com|notSent |0 |0
2 |Nick |J |group |2019-02-11|2019-11-11|2 |821234124|test address |AS|test@mydomain.com|notSent |0 |0
3 |Mars |M |group |2019-02-08|2019-11-09|2 |821234124|test address |AS|test@mydomain.com|sent |0 |0
4 |Nicole |M |group |2019-02-07|2019-11-10|5 |54546456 |test address |AS|test@mydomain.com|sent |0 |0
5 |Tim |M |group |2019-02-08|2019-11-11|2 |821234124|test address |AS|test@mydomain.com|sent |0 |0
6 |Sue |h |group |2019-02-09|2019-11-09|3 |34534534 |test address |AS|test@mydomain.com|sent |0 |0
7 |carl |M |group |2019-02-10|2019-11-10|8 |54546456 |test address |AS|test@mydomain.com|sent |0 |0
8 |peter |M |private |2019-02-11|2019-11-11|2 |54546456 |test address |GC|test@mydomain.com|sent |0 |0
9 |jim |M |private |2019-02-12|2019-11-09|6 |54546456 |test address |GC|test@mydomain.com|notSent |0 |0
10 |bianca |M |private |2019-02-13|2019-11-10|3 |54546456 |test address |GC|test@mydomain.com|notSent |0 |0
11 |richman|M |private |2019-02-14|2019-11-11|2 |54546456 |test address |GC|test@mydomain.com|notSent |0 |0
代码
const fs = require('fs');
const readline = require('readline');
const { google } = require('googleapis');
const SCOPES = "https://www.googleapis.com/auth/drive";
const TOKEN_PATH = 'token.json';
fs.readFile('credentials.json',(err,content) => {
if (err) return console.log('Error loading client secret file:',err);
authorize(JSON.parse(content),listMajors);
});
/**
* Create an OAuth2 client with the given credentials,and then execute the
* given callback function.
* @param {Object} credentials The authorization client credentials.
* @param {function} callback The callback to call with the authorized client.
*/
function authorize(credentials,callback) {
const { client_secret,client_id,redirect_uris } = credentials.installed;
const oAuth2Client = new google.auth.OAuth2(
client_id,client_secret,redirect_uris[0]);
fs.readFile(TOKEN_PATH,token) => {
if (err) return getNewToken(oAuth2Client,callback);
oAuth2Client.setCredentials(JSON.parse(token));
callback(oAuth2Client);
});
}
/**
* Get and store new token after prompting for user authorization,and then
* execute the given callback with the authorized OAuth2 client.
* @param {google.auth.OAuth2} oAuth2Client The OAuth2 client to get token for.
* @param {getEventsCallback} callback The callback for the authorized client.
*/
function getNewToken(oAuth2Client,callback) {
const authUrl = oAuth2Client.generateAuthUrl({
access_type: 'offline',scope: SCOPES,});
console.log('Authorize this app by visiting this url:',authUrl);
const rl = readline.createInterface({
input: process.stdin,output: process.stdout,});
rl.question('Enter the code from that page here: ',(code) => {
rl.close();
oAuth2Client.getToken(code,token) => {
if (err) return console.error('Error while trying to retrieve access token',err);
oAuth2Client.setCredentials(token);
// Store the token to disk for later program executions
fs.writeFile(TOKEN_PATH,JSON.stringify(token),(err) => {
if (err) return console.error(err);
console.log('Token stored to',TOKEN_PATH);
});
callback(oAuth2Client);
});
});
}
/**
* Prints the names and majors of students in a sample spreadsheet:
* @see https://docs.google.com/spreadsheets/d/xxxxxxxxxxx/edit
* @param {google.auth.OAuth2} auth The authenticated Google OAuth client.
*/
function listMajors(auth) {
var today = new Date();
today = today.toISOString().slice(0,10);
var dateYesterday = new Date();
dateYesterday.setDate(dateYesterday.getDate() - 1);
dateYesterday = dateYesterday.toISOString().slice(0,10);
var dayYesterday = new Date();
var days = [
"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"
];
dayYesterday.setDate(dayYesterday.getDate() - 1);
dayYesterday = days[dayYesterday.getDay()];
var dateTomorrow = new Date();
dateTomorrow.setDate(dateTomorrow.getDate() + 1);
dateTomorrow = dateTomorrow.toISOString().slice(0,10);
var dayTomorrow = new Date();
var futureDays = [
"Sunday","Saturday"
];
dayTomorrow.setDate(dayTomorrow.getDate() + 1);
dayTomorrow = futureDays[dayTomorrow.getDay()];
const sheets = google.sheets({ version: 'v4',auth });
sheets.spreadsheets.values.get({
spreadsheetId: 'xxxxxxxxxxx',range: 'A2:N',},res) => {
if (err) return console.log('The API returned an error: ' + err);
const rows = res.data.values;
var data = res.data.values;
var filteredRows = data.filter(eachRow);
function eachRow(eachRow) {
return (eachRow[13] === '0' || 'undefined') && eachRow[5] === dateYesterday; //2019-02-17
}
if (filteredRows != 0) {
for (x = 0; x < filteredRows.length; x++) {
console.log(filteredRows[x].join());
console.log(filteredRows[x][0]);
// let selectedRowIndex = filteredRows[x][0]+1 ;
let selectedRowIndex = rows.indexOf(filteredRows[x]);
console.log("Index: " + selectedRowIndex);
////////////////////UPDATING START////////////////////////////
fs.readFile('credentials.json',content) => {
if (err) return console.log('Error loading client secret file:',err);
// Authorize a client with credentials,then call the Google Sheets API.
authorize(JSON.parse(content),updateCells);
});
/**
* Prints the names and majors of students in a sample spreadsheet:
* @see https://docs.google.com/spreadsheets/d/xxxxxx/edit
* @param {google.auth.OAuth2} auth The authenticated Google OAuth client.
*/
function updateCells(auth) {
var sheets = google.sheets({ version: 'v4' });
authorize(function () {
var request = {
spreadsheetId: 'xxxxxxxxx',//
valueInputOption: 'RAW',// TODO: Update placeholder value.
resource: {
"data": [
{
"range": "N" + selectedRowIndex,"majorDimension": "COLUMNS","values": [
[
1
],]
}
]
},auth,};
console.log(selectedRowIndex);
sheets.spreadsheets.values.batchUpdate(request,function (err,response) {
if (err) {
console.error(err);
return;
}
console.log(JSON.stringify(response,null,2));
});
});
function authorize(callback) {
var auth = "https://www.googleapis.com/auth/drive";
if (auth == null) {
console.log('authentication failed');
return;
}
callback(auth);
}
}
////////////////////UPDATING END////////////////////////////
}
};
});
}
运行后的数据
idRef|fName |lName|tourType|dateBooked|tourDate |Pax|phone |pickupAddress|Op|emailaddress |bookedSent|reminderSent|feedbackSent
1 |Josh |w |group |2019-02-12|2019-11-09|3 |821467371|test address |AS|test@mydomain.com|notSent |0 |0
12 |jess |s |group |2019-02-22|2019-11-10|2 |1233333 |test address |as|test@mydomain.com|notSent |0 |1
2 |Nick |J |group |2019-02-11|2019-11-11|2 |821234124|test address |AS|test@mydomain.com|notSent |0 |0
3 |Mars |M |group |2019-02-08|2019-11-09|2 |821234124|test address |AS|test@mydomain.com|sent |0 |0
4 |Nicole |M |group |2019-02-07|2019-11-10|5 |54546456 |test address |AS|test@mydomain.com|sent |0 |1
5 |Tim |M |group |2019-02-08|2019-11-11|2 |821234124|test address |AS|test@mydomain.com|sent |0 |0
6 |Sue |h |group |2019-02-09|2019-11-09|3 |34534534 |test address |AS|test@mydomain.com|sent |0 |0
7 |carl |M |group |2019-02-10|2019-11-10|8 |54546456 |test address |AS|test@mydomain.com|sent |0 |1
8 |peter |M |private |2019-02-11|2019-11-11|2 |54546456 |test address |GC|test@mydomain.com|sent |0 |0
9 |jim |M |private |2019-02-12|2019-11-09|6 |54546456 |test address |GC|test@mydomain.com|notSent |0 |0
10 |bianca |M |private |2019-02-13|2019-11-10|3 |54546456 |test address |GC|test@mydomain.com|notSent |0 |0
11 |richman|M |private |2019-02-14|2019-11-11|2 |54546456 |test address |GC|test@mydomain.com|notSent |0 |0
如您所见，代码将更新第N行的单元格，但不会更新正确的索引。 IE上面的代码应将col F = 2019-11-09（至昨天的相对日期）的每个col N更新为值1，但每次都将正确的行跳过1。
在updateCells batchUpdate函数中，我正在调用"range": "N" + selectedRowIndex,
我能够在运行时更新多个单元格，但是selectedRowIndex不与正确的行实际位置（val 5 =昨天的日期的所有行）匹配。我通过使用函数调用从稍高一些的for循环范围之外无法访问该变量开始摸索，但也没有运气。我认为问题似乎是第一行是冻结行。在我的上层函数中， listMajors range: 'A2:N',在没有标题单元格的情况下调用正确的数据，但是如何将此起点应用于第二个batchUpdate函数。还是我需要映射一个新数组或其他东西？没有办法像sheets.value.get函数那样指定范围吗？
请问，selectedRowIndex为什么不选择并更新正确的行？