Discover air quality in your city with... šŸŒ¬ qualitAir!

Hi there,
Iā€™m excited to show you my new app qualitAir! :rocket:

https://r2g6x.glideapp.io/
Helps you checking the air quality in your city


:warning: 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 :mag:)
  • 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? :mage: help wanted)

The app is not quite stable, I need your help to testing! :pray:
Any feedback is appreciated :slightly_smiling_face:
Thank you

5 Likes

Hi Vicio,

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?

1 Like

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 components :slightly_frowning_face: Right now, for example, the user will have to choice or scroll down an infinite list of 195 countries without help. And sadly there is also Canā€™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 :expressionless:
Any idea?

1 Like

They only differ by the Unsplash link right? For some reason Cloudinary canā€™t fetch the first one.

You can search within a choice componenent:

1 Like

Thanks man! Works fine for countries entry. Never tried before :slightly_smiling_face:

1 Like

@Vicio_Sciascia nice and clean app.
In info tab you have many empty cells resulting in many empty cards.

Iā€™m interested in learning more about how you work with apiā€™s. Maybe you would share on a glide meetup? @nathanaelb

Thx for sharing

2 Likes

Thanks my friend, now the emty cards issue should be fixed :slightly_smiling_face:

Sure I can try, keep in touch! Whatā€™s the schedule of upcoming meetups?

:warning: Right now Iā€™m trying to replace my buggy Apps Script watcher with an Integromat scenario, so the app will not work for a while ā€¦sorry about that! :sweat_smile:

@Vicio_Sciascia @nathanaelb we havenā€™t fixed a new date for the next meetup but when will you be ready to show your setup / experience with apiā€™s?

1 Like

This is definitely a thing I want to learn in our next meetup. Tell us if we can help with your script Vicio.

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 :sweat_smile:

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ā€™):

You already know how to work with it, so hereā€™s my links to scripts for copying the formula down when ARRAYFORMULA doesnā€™t work.

Hope it helps.

2 Likes

Thanks @ThinhDinh, so much better than my script!

After a substantial refactor everything seems to work fine now, unless the issue about cloudinary failing to fetch some photos :frowning:

Feel free to try now! :rocket:
Thank you

P.S.: canā€™t I edit my open post to remove the ā€œWarningā€ desclaimer?

image

Can you see this button? You can use it to edit your post.

No man, I think thatā€™s because its a post i made 5 days ago :frowning:

Thatā€™s weird. No idea :no_mouth:

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:

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%3A%2F%2Fimages.unsplash.com%2Fphoto-1539785049334-763b093d98bb%3Fixlib%3Drb-1.2.1%26q%3D80%26fm%3Djpg%26crop%3Dentropy%26cs%3Dtinysrgb%26w%3D1080%26fit%3Dmax%26ixid%3DeyJhcHBfaWQiOjEzNjQ4M30

:new: Donā€™t miss out the new city detail page with weather data :slightly_smiling_face:

1 Like