This is obviously a broad question. I’m not a historian of Spain, so I can’t really talk about how the colonies actually affected Spain itself in the eighteenth century or its economy. But I can talk about the Americas in more detail, which is a major part of your question. Much of the actual maintenance and expansion of empire happened at the local level, after all.

But at its most basic, the eighteenth century in the Americas was a giant struggle for Atlantic domination between many European powers. Rather than being irrelevant to European geostrategic concerns, Spanish colonies were a central component of European geostrategic maneuverings. For example, a lot of wars that played out on the European continent and were also fought in the Americas, sometimes even starting there (e.g. War of Jenkin’s Ear, Seven Years’ War, and the American Revolution). Spain was very much a participant in this global game of geostrategic chess, just as much as any other European power, and they often came out on top or in relative draws. I've found that labeling Spain a second rate power seems subjective at best, perhaps more true on the European continent itself, especially on its battlefields, than in the Americas more broadly. Yes, following the War of Spanish Succession, the new Bourbon monarchy did implement a century of reforms, known as the Bourbon Reforms, which sought to improve trade, bolster defenses, and centralize administration in the Americas, and yes, British and French power had grown immensely. But throughout the eighteenth century, Spain consistently kept up with other European powers in the Atlantic.

I can identify perhaps three-fold reasons that the Spanish colonies appear less meaningful to broader geostrategic concerns: 1.) There were simply more zones of European rivalries than there were in the early sixteenth century, when Spain had a small, tedious hold in Central Mexico and Peru, Portugal was largely limited to the very edge of the South American continent, and the English were largely still wrapped up in Tudor and Stewart struggles. Much of the rest of the European continent was only just coming out of the Reformation, and the general locus of European attention was only just starting to shift away from the Mediterranean towards the Atlantic. By the eighteenth century, the global networks of trade were far more complex and had many more participants. The treasure fleets were less central to the global economy as a whole, and the mines at Potosí no longer commanded the same market share and windfalls as they did in the sixteenth century. Slavery and plantation agriculture was far more important and profitable. 2.) The idea that Spain was irrelevant crept into historical literature as a result of the Black Legend, which denigrated the Spanish Empire as a dogmatic Catholic culture maintained solely by cruelty to indigenous people. This narrative was especially popular in the mid-seventeenth, eighteenth, and nineteenth centuries and remains in archival documents. For instance, the idea that Spain “jealously guarded” its colonies, is written in English archival documents during the seventeenth century. Such statements wrote off Spanish power as illegitimate and irrelevant. 3.) The second rate power interpretation was also amplified when Latin American countries themselves became independent. One of the ways that independence-era nation builders justified their early nation building projects in the nineteenth century was by explicitly criticizing the backwardness of the Spanish Empire. In order for them to advance platforms of “progress,” they played up the burden of a Spanish “colonial legacy” that they had to overcome.

So what are some ways that Spain's colonies were still central to Atlantic concerns?

In South America during the eighteenth century, struggles between Spain and Portugal led to a greater clarification of the borderland in the Río de la Plata region. The Portuguese established a firmer presence over the Banda Oriental (now Uruguay), which led to contestations over where to delineate the two empires. This territory switched hands several times.

In the Caribbean and North America, Britain, France, and Spain were the preeminent contestants vying for control and influence. Islands and territories frequently switched hands as a result of wars and treaty negotiations. For example, Louisiana territory switched hands numerous times between France and Spain. Add into this mix the Dutch, Swedes, and Danes, who all had colonies in the Caribbean. It appears that the defenders in wars generally held the upper hand when invaded, especially when fighting armies formed in Europe, which were quickly decimated by disease. Yet, these colonies were all closely intertwined by transimperial trade, especially maritime-based slave trading and slave transshipment. There was also a huge amount of smuggling that went between colonies. The Spanish Empire was never closed. The importance of the galleons as the “only” form of Spanish trade is incorrect. There were always lots of smaller boats sailing between the ports in the Atlantic World, all of which were carrying trade goods, mail, and contraband.

Northern New Spain appears one of the central drivers of the world economy before the Industrial Revolution in Britain and the British shift towards India. John Tutino in his book Making a New World argues that the Bajío (Querétaro and Northern New Spain) was a key capitalist site and a contributor to the development of global capitalism. The colossal silver production happening there and the industries needed to support this mining and export made Northern New Spain a motor of the world economy, one of two major developed capitalist sites in the Americas, along with Haiti. Most of this silver ended up in China, with huge amounts of Chinese goods and people being brought back across the Pacific to the Americas. Going along with this idea, huge amounts of New World agricultural products, by the eighteenth century, had taken hold in the cuisines and cultures of Europe. Things like chocolate, luxury goods, spices, and dyes all transformed European material culture. If we step beyond the idea of hard power, the influence of Spanish colonies is all over Europe.

Moreover, to some extent the sorting of European hard power into first rate powers and second rate powers is itself a European colonialist fiction. The overwhelming majority of people crossing the Atlantic during the eighteenth century were enslaved Africans, and a lot of the wealth and power of European empires were a direct result of this abhorrent and cruel practice. Also, because of the size of the African populations, it meant that African culture, language, foodstuffs, and knowledge were all crucial elements of the day-to-day life of individuals in the Americas, and consequently, imperial networks of power and knowledge were infused with African knowledge and culture, even if we don’t think about it much today. Also, the vast majority of the territory of the Americas remained firmly in indigenous hands.
More than half of the Americas was autonomous indigenous territory, and much of the Spanish Empire itself remained a porous sea of “islands” of Spanish domination surrounded by semi-controlled indigenous communities. Indigenous people made up the vast majority of the population of the Americas and were not as Europeanized, Christianized, or Hispanicized as one might expect.