I have the following data in a google sheet and I attempting to fetch the update each row (N) where array value 5 matches yesterdays date. (Please note I had to csv the data in order to paste it here properly but it is in an excel sheet with the top row frozen)
Data before running
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|[email protected]|notSent |0 |0
12 |Jess |s |group |2019-02-22|2019-11-10|2 |1233333 |test address |as|[email protected]|notSent |0 |0
2 |Nick |J |group |2019-02-11|2019-11-11|2 |821234124|test address |AS|[email protected]|notSent |0 |0
3 |Mars |M |group |2019-02-08|2019-11-09|2 |821234124|test address |AS|[email protected]|sent |0 |0
4 |Nicole |M |group |2019-02-07|2019-11-10|5 |54546456 |test address |AS|[email protected]|sent |0 |0
5 |Tim |M |group |2019-02-08|2019-11-11|2 |821234124|test address |AS|[email protected]|sent |0 |0
6 |Sue |h |group |2019-02-09|2019-11-09|3 |34534534 |test address |AS|[email protected]|sent |0 |0
7 |carl |M |group |2019-02-10|2019-11-10|8 |54546456 |test address |AS|[email protected]|sent |0 |0
8 |peter |M |private |2019-02-11|2019-11-11|2 |54546456 |test address |GC|[email protected]|sent |0 |0
9 |jim |M |private |2019-02-12|2019-11-09|6 |54546456 |test address |GC|[email protected]|notSent |0 |0
10 |bianca |M |private |2019-02-13|2019-11-10|3 |54546456 |test address |GC|[email protected]|notSent |0 |0
11 |richman|M |private |2019-02-14|2019-11-11|2 |54546456 |test address |GC|[email protected]|notSent |0 |0
Code
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, (err, 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, (err, 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", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "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',
}, (err, 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', (err, 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////////////////////////////
}
};
});
}
Data after running
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|[email protected]|notSent |0 |0
12 |Jess |s |group |2019-02-22|2019-11-10|2 |1233333 |test address |as|[email protected]|notSent |0 |1
2 |Nick |J |group |2019-02-11|2019-11-11|2 |821234124|test address |AS|[email protected]|notSent |0 |0
3 |Mars |M |group |2019-02-08|2019-11-09|2 |821234124|test address |AS|[email protected]|sent |0 |0
4 |Nicole |M |group |2019-02-07|2019-11-10|5 |54546456 |test address |AS|[email protected]|sent |0 |1
5 |Tim |M |group |2019-02-08|2019-11-11|2 |821234124|test address |AS|[email protected]|sent |0 |0
6 |Sue |h |group |2019-02-09|2019-11-09|3 |34534534 |test address |AS|[email protected]|sent |0 |0
7 |carl |M |group |2019-02-10|2019-11-10|8 |54546456 |test address |AS|[email protected]|sent |0 |1
8 |peter |M |private |2019-02-11|2019-11-11|2 |54546456 |test address |GC|[email protected]|sent |0 |0
9 |jim |M |private |2019-02-12|2019-11-09|6 |54546456 |test address |GC|[email protected]|notSent |0 |0
10 |bianca |M |private |2019-02-13|2019-11-10|3 |54546456 |test address |GC|[email protected]|notSent |0 |0
11 |richman|M |private |2019-02-14|2019-11-11|2 |54546456 |test address |GC|[email protected]|notSent |0 |0
As you can see, the code updates the cells in col N but not at the correct index. IE the above should update each col N where col F = 2019-11-09 (to yesterdays relative date) to value 1 but it is skipping the correct row by 1 each time.
In the updateCells batchUpdate function I am calling "range": "N" + selectedRowIndex,
I am able to update multiple cells upon running however the selectedRowIndex isnt matching up with the correct rows actual location (all rows where val 5 = yesterdays date). I scratched around a bit with the variable being inaccessible from outside the scope of the for loop located a little higher up by using a function call but no luck either. The problem I think seems to be that the first row is a frozen row. In my function higher up, listMajors range: 'A2:N', calls the correct data without the header cell but how can I apply this starting point to my second batchUpdate function. Or do I need to map a new array or something? Is there not a way to specific the range like with the sheets.value.get function?
Please, why doesn't selectedRowIndex select and update the correct rows?
12,Jess,s,group,2019-02-22,2019-11-10,2,1233333,test address,as,[email protected],notSent,0,1,4,Nicole,M,group,2019-02-07,2019-11-10,5,54546456,test address,AS,[email protected],sent,0,1,7,carl,M,group,2019-02-10,2019-11-10,8,54546456,test address,AS,[email protected],sent,0,1was modified from0to1. - Tanaike2019-11-11, when the value of the column "F" is2019-11-10which is yesterday, the last column is modified to1. Is my understanding correct? - Tanaike