I need air

This preposition is both simple and annoying. Let's start with the simple:

mi	thu	e	i	sinn	sibh	iad
orm	ort	air	oirre	oirnn	oirbh	orra
[ɔrɔm]	[ɔRʃd]	[ɛrʲ]	[ɔRə]	[ɔːRNʲ]	[ɔrʲɪv]	[ɔRə]

Yes, oirre and orra are pronounced the same way because there no longer a disctinction between broad and slender rr in modern Scottish Gaelic. But that's only very rarely confusing in context.

Air, lots of air

Unfortunately, yes. You'll most likely first meet air meaning "on" like air bòrd "on board" and be told that there's no lenition after air. Yay, you think. The you run into deich air fhichead and you just file it under it's a number, maybe a special case. Then you bump into beag air bheag and maybe it looks a bit strange but you just learn it as a phrase. But as you progress and run into air chor-eigin and air bheag airgid you begin to wonder about the "no lenition", not to mention that many of these don't really fit the translation of "on".

Your confusion is justified. What makes this so confusing is that what looks like air is actually the result of 4 different words all ending up with the same spelling. Don't worry, this isn't going to be as bad as The many functions of a.

#1 – On

The easy one first. In the sense of "on", air comes from Old Irish for (which went form, fort, for, foir etc) and didn't lenite in Old Irish either. Hence the following with no lenition (without the definite article or possessives of course):

air bòrd "on a table"
air ceann "on a head"
air màthraichean "on mothers"

etc

#2 – After

The next one is air in the sense of "after". This also doesn't cause lenition and comes from Old Irish íar. In Old Irish, íar caused nasalization but as that disappeared from Scottish Gaelic, we were left with it just not doing anything.

air tuiteam "after falling, having fallen"
air bruidhinn "after speaking, having spoken"
air falbh "after leaving, having left, gone, away"

#3 – Over/Past

The last fairly simple one. This one ultimately comes from thar "over, past" which traditionally lenites and takes the genitive and (though not always consistently):

thar chuantan/chuan "across the ocean(s)"
thar cheann "altogether, on average"
thar bheann "over the mountains" (i.e. the other side)

Mostly you'll meet this in the form of ar or air in numbers involving twenty: deich ar/air fhichead, so literally "10 past 20".

#4 – The tricky one

There always is one, isn't there? So there's a fourth air which comes from Old Irish ar where it caused lenition. It covered a wide range of meanings such as "for the sake of/on behalf; because of/on account of, instead of; in exchange for; by means of/through; relating to; as a result of; in spite of". And by and large, it still covers most of these functions though many of them are now relegates to fixed phrases.

Expressions describing a state

If - from the Gaelic point of view - you're describing a state and Gaelic uses an air, then it's usually the fourth air with lenition. It's a bit like a linguistic version of a looping GIF.

air bhoil "in a rage"
air bhàinidh "in a rage/frenzy"
air chuairt "on a trip"
air chrith "shaking"
air bhog "floating"
air chèilidh "visiting"

and many more

beag air bheag

This is perhaps the second most productive category (meaning you can still make new combinations of this sort). It follows a pattern of adjective/noun + air + lenited adjective/noun:

mean air mhean "little by little"
beag air bheag "little by little"
ceum air cheum "step by step"
pìos air phìos "piece by piece"

or other

This category has exactly one member: air chor-eigin (if you count dialect forms like air chor-eiginich as being the same word really).

by means of

In the sense of "by means of/through" and "in exchange for", there are quite a few idioms that you might well hear:

air bheag(an) airgid "through little money"
air mhòr(an) fhiachan "through many debts"
air bheag(an) eòlais "by the means of a little knowledge"
mair fhortan "(in exchange) for a fortune"

in spite of

air ghainnead an airgid "in spite of the lack of money"
air phailteas d' airgid "in spite of your abundance of money"

You can look at it this way too - if you run into air and it seems a bit odd and isn't case 1, 2 or 3 above, it's most likely number 4!

Roimhearan

᚛ á - aig - air - ann an - de ⁊ a - do ⁊ a - eadar - fo - gu - le - mu - o ⁊ bho - os ⁊ fos - ri - tro - thar ᚜