Comparing Date/Time using Google Query on a Sheet

[Raymond Wood]

Hey all.

I have a Google sheet that takes responses from a form. I want to return items in column A, B, C, R where column R is not null and column A (a timestamp is more recent than one week ago.  On top of that I am not sure how to pull the data from the JSON object.

function queryMaintLog() {

const ssId = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

const sheetName = 'Ride Log';

const timeStamp = 'A';

const email = 'B';

const name = 'C';

const maintLog = 'R';

const lastWeek = new Date().getTime()-7*(24*3600*1000);

const query = `SELECT ${timeStamp}, ${email}, ${name}, ${maintLog} WHERE ${maintLog} <> '' AND ${timeStamp} >= ${lastWeek}`;

// CREATE THE QUERY URL

const qvizURL = 'https://docs.google.com/spreadsheets/d/' + ssId + '/gviz/tq?tqx=out:json&headers=1&sheet=' + sheetName + '&range=' + timeStamp + ":" + maintLog + '&tq=' + encodeURIComponent(query);

Logger.log(qvizURL);

// MAKE THE QUERY

const ret = UrlFetchApp.fetch(qvizURL, {headers: {Authorization: 'Bearer ' + ScriptApp.getOAuthToken()}}).getContentText();

Logger.log(ret);

// PARSE THE QUERY

const result = JSON.parse(ret.replace("/*O_o*/", "").replace("google.visualization.Query.setResponse(", "").slice(0, -2));

Logger.log(result);

}

queryMaintLog();

The following was returned (note only headers were returned because the query is not right)

10:45:43 AM

Info

https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/gviz/tq?tqx=out:json&headers=1&sheet=Ride Log&range=A:R&tq=SELECT%20A%2C%20B%2C%20C%2C%20R%20WHERE%20R%20%3C%3E%20''%20AND%20A%20%3E%3D%201666287943350

10:45:43 AM

Info

/*O_o*/ google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"1960466594","table":{"cols":[{"id":"A","label":"Timestamp","type":"datetime","pattern":"dd/MM/yyyy HH:mm:ss"},{"id":"B","label":"Email address","type":"string"},{"id":"C","label":"Pilot Name","type":"string"},{"id":"R","label":"Report any Maintenance Issues Here (not required)","type":"string"}],"rows":[],"parsedNumHeaders":0}});

10:45:43 AM

Info

{reqId=0, version=0.6, sig=1960466594, table={cols=[{type=datetime, id=A, label=Timestamp, pattern=dd/MM/yyyy HH:mm:ss}, {id=B, type=string, label=Email address}, {label=Pilot Name, type=string, id=C}, {label=Report any Maintenance Issues Here (not required), type=string, id=R}], rows=[], parsedNumHeaders=0.0}, status=ok}

Any help would be great. Thank you.

[Raymond Wood]

I figured out how to retrieve the data from the object but still don't know how to compare dates in a query.

[cbmserv...@gmail.com]

Any reason you are using the URL API rather than directly using the Google AppsScript Spreadsheet API?

 

You can easily read all the data in a spreadsheet by using the Spreadsheet API.

 

Here is a link to the Spreadsheet API:

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/26fc2a20-d44b-4b63-8d73-b9e53f5737fdn%40googlegroups.com.

[Raymond Wood]

Hey George.

I am accessing a log type worksheet that will grow huge so I don't want copy the entire sheet to a table and then iterate though it. I would prefer to query the sheet and return the few records that I need.

I may be mistaken