Right now Iām trying to upgrade my buggy Apps Script watcher or replacing it with an Integromat scenario, so the app will not work for a while ā¦sorry about that!
Here the recipe
ImportJSON to ā¦import JSON data from APIs
Geocode.xyz APIs to retrieve lat long from an address
IQAir AirVisual APIs to retrieve pollution and weather data from lat long
Unsplash APIs to show beautiful images of cities
Cloudinary to ovelay air quality level on top of city image
Custom Google Apps Script watcher that triggers data retrieval when a new search is made
Known issues
Image not loading for some cities (something related to Cloudinary, investigating )
AirVisual APIs sometimes gives results about a city near the one you searched (although they actually have data about the city you searched)
Maybe my watcher needs some code-review to be more efficient, Iāll share it in a comment below (any script magician? help wanted)
The app is not quite stable, I need your help to testing!
Any feedback is appreciated
Thank you
I think this is a good use of APIs and Cloudinary, well done!
For a better UX, I think you may try having two choice components. First choose country, then the second one is a relation that returns all cities from that country, rather than letting the user input it. What do you think about that?
Hi @ThinhDinh,
thank you! Itās surely a great idea [edit] and can be done for countries (thanks @Jeff_Hager, i was wrong, the user will be helped with a search bar automatically added by glide)if only we could have text research in choice componentsRight now, for example, the user will have to choice or scroll down an infinite list of 195 countries without help. And sadly there is alsoCanāt do the same for cities because there is the 500 rows limit to take into account!
I think you experienced issue #1 when searching London:
First time you searched, the cloudinary link was: https://res.cloudinary.com/vsciascia/image/fetch/w_600/w_0.35,c_scale,fl_relative,l_text:Zilla%20Slab%20Highlight_64_bold_hinting_full:Good,co_rgb:97c05b/https://images.unsplash.com/photo-1513635269975-59663e0ac1ad?ixlib=rb-1.2.1&q=80&fm=jpg&crop=entropy&cs=tinysrgb&w=1080&fit=max&ixid=eyJhcHBfaWQiOjE0MjM5NX0
Second time you searched, the link was: https://res.cloudinary.com/vsciascia/image/fetch/w_600/w_0.35,c_scale,fl_relative,l_text:Zilla%20Slab%20Highlight_64_bold_hinting_full:Good,co_rgb:97c05b/https://images.unsplash.com/photo-1532793962127-d6735aba5cce?ixlib=rb-1.2.1&q=80&fm=jpg&crop=entropy&cs=tinysrgb&w=1080&fit=max&ixid=eyJhcHBfaWQiOjE0MjM5NX0
I canāt figure out why the first one gives an error, while the second works fine
Any idea?
Hi folks,
here is the script, I added some comments to explain what Iām trying to do.
Iām sure this is a mess, i suck at coding so please help
WHY Iām doing so: because I havenāt found a way for ImportJSON to work with ARRAYFORMULA.
I setup this function:
function onAdd(e) {
// I setup a named range for cells C1:C named 'triggerRange', that's where the city queries are stored
// What I was trying to do is: if a new record is created or something changes in the triggerRange -> do the magic below, else -> do nothing
// This triggerRange if / else part doesn't work and so is commented
// var myRange = SpreadsheetApp.getActiveSheet().getRange('triggerRange');
// var row = e.range.getRow();
// var col = e.range.getColumn();
// if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= myRange.getRow() && row <= myRange.getLastRow()) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Locations');
var lastRow = sheet.getLastRow(); // get the number of rows in the main sheet i called 'Locations'
// SpreadsheetApp.getUi().alert('Last row is ' + lastRow); // this is only for manual testing on the sheet, does not work with Google App Scripts triggers
var firstCell = sheet.setActiveCell('C1'); // select to the header of cities column
var firstCellRow = firstCell.getRow(); //
var firstCellCol = firstCell.getColumn();
// SpreadsheetApp.getUi().alert('Cell selected row is ' + firstCellRow + ' and the Column is ' + firstCellCol); // as mentioned on line 14
var lastRequest = firstCell.offset(lastRow-1, 0); // offset to the last city entry
var lastRequestRow = lastRequest.getRow(); // get the row of the last city entry
var lastRequestCol = lastRequest.getColumn(); // get also the column
// SpreadsheetApp.getUi().alert('Active cell row is ' + lastRequestRow + ' and the column is ' + lastRequestCol); // as mentioned on line 14
// From the last city entry, offset 2 columns left to put the result of geocode.xyz API call = lat and long of the city
var locationCell = lastRequest.offset(0, 2);
var locationCellRow = locationCell.getRow();
var locationCellCol = locationCell.getColumn();
var locationFormula = "=ImportJSON(\"https://geocode.xyz/\"&C"+locationCellRow+"&\"+\"&D"+locationCellRow+"&\"&auth={{myGEOCODE_API_KEY}}}?json=1\",\"/longt,/latt\",\"noHeaders\")";
locationCell.setFormula(locationFormula);
Utilities.sleep(3000); // sleeping for 3 seconds to prevent errors on the next API call
// From the last city entry, offset 4 columns left to put the result of AirVisual API call = air pollution and weather conditions
var pollutionCell = lastRequest.offset(0, 4);
var pollutionCellRow = pollutionCell.getRow();
var pollutionCellCol = pollutionCell.getColumn();
var pollutionFormula = "=ImportJSON(\"https://api.airvisual.com/v2/nearest_city?lat=\"&F"+pollutionCellRow+"&\"&lon=\"&E"+locationCellRow+"&\"&key={{myAIRVISUAL_API_KEY}}\",\"\",\"noHeaders\")";
pollutionCell.setFormula(pollutionFormula);
Utilities.sleep(2000); // sleeping for 2 seconds to prevent errors on the next API call
// From the last city entry, offset 29 columns left to put the result of Unsplash API call = an image related to the city
var pictureCell = lastRequest.offset(0, 29);
var pictureCellRow = pictureCell.getRow();
var pictureCellCol = pictureCell.getColumn();
var pictureFormula = "=ImportJSON(\"https://api.unsplash.com/photos/random/?query=\"&C"+pictureCellRow+"&\"&per_page=1&orientation=landscape&client_id={{myUNSPLASH_CLIEND_ID}}}\",\"/urls/regular\",\"noInherit,noHeaders\")";
pictureCell.setFormula(pictureFormula);
// } else {
// return;
// }
}
And then setup a trigger for that (my UI is in italian, the event trigger should be āOn Changeā):
Okay I made some tests and apparently the cloudinary fetch issue is solved by using ENCODEURL function on the url of the image I was trying to fetch, for example: