Discover air quality in your city with... 🌬 qualitAir!

[Vicio_Sciascia]

Hi there,
I’m excited to show you my new app qualitAir!

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

---

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

[ThinhDinh]

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?

[Vicio_Sciascia]

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 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
Any idea?

[ThinhDinh]

@Vicio_Sciascia

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

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

[Jeff_Hager]

@Vicio_Sciascia

text research in choice components

You can search within a choice componenent:

[Vicio_Sciascia]

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

[Krivo]

@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

[Vicio_Sciascia]

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

@Krivo

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

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

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!

[Krivo]

@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?

[ThinhDinh]

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

[Vicio_Sciascia]

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’):

[ThinhDinh]

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.

Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts

Hi, Today I will show you the script that can make copying formulas down work, when it does not work with arrayformula. But first, let’s have some introductions about arrayformula, for those who has not used this before in Google Sheets, some equivalents in Glide and some good practices while using it. I hope it can help you in building your Glide apps as well as applying it in your normal day job.

1/ Definition

Arrayformula is defined as “a range, mathematical expression using one cell range …

Hope it helps.

[Vicio_Sciascia]

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

Feel free to try now!
Thank you

P.S.: can’t I edit my open post to remove the “Warning” desclaimer?

[ThinhDinh]

@Vicio_Sciascia

P.S.: can’t I edit my open post to remove the “Warning” desclaimer?

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

[Vicio_Sciascia]

No man, I think that’s because its a post i made 5 days ago

[ThinhDinh]

That’s weird. No idea

[Vicio_Sciascia]

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

Don’t miss out the new city detail page with weather data