Blog

Amazon's return process is second to none. It is one of the very few large e-commerce sites that gets the process right and makes the process painless for its customers. Along with their customer support, I've never had an issue in returning an item if the quality of the item was not up to standard or received damaged. But in such cases just because you can return an item should you?

Ever since I read articles on Amazon destroying millions of items of unsold stock just to be sent to landfill, I've been more mindful as to when I should return an item.

If I receive an item that slightly damaged, I normally opt for a discount rather than sending it back. I've done this in the past by either contacting the third-party seller or Amazon customer service using their online chat tool. They are very forthcoming in offering a discount as long as you can provide proof that the product you received is damaged or not in an acceptable condition.

This approach has worked for me whenever I felt it was required. Not surprising when you take into consideration the cost to the seller for pickup and disposal (or renewing the product).

I normally opt for this approach for items that are still fit for purpose, where the damage can easily be hidden and the longevity is not compromised. Of course, the level of damage that is deemed acceptable depends on the product and your own personal view.

Why not try this alternative the next time you receive a damaged product that is still able to serve its purpose? If enough of us are able to take this route, it can not only benefit the environment but also reduce waste.

I've been delving further into the world of Google App Scripts and finding it my go-to when having to carry out any form of data manipulation. I don't think I've ever needed to develop a custom C# based import tool to handle the sanitisation and restructuring of data ever since learning the Google App Script approach.

In this post, I will be discussing how to search for a value within a Google Sheet and return all columns within the row the searched value resides. As an example, let's take a few columns from a dataset of ISO-3166 Country and Region codes as provided by this CSV file and place them in a Google Sheet named "Country Data".

The "Country Data" sheet should have the following structure:

App Script 1: Returning A Single Row Value

Our script will be retrieving the two-letter country code by the country name - in this case "Australia". To do this, the following will be carried out:

1. Perform a search on the "Country Data" sheet using the findAll() function.
2. The getRow() function will return single row containing all country information.
3. A combination of getLastColumn() and getRange() functions will output values from the row.

function run() {
  var twoLetterIsoCode = getCountryTwoLetterIsoCode("Australia"); 
}

function getCountryTwoLetterIsoCode(countryName) {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var countryDataSheet = activeSheet.getSheetByName('Country Data');

  // Find text within sheet.
  var textSearch = countryDataSheet.createTextFinder(countryName).findAll();

  if (textSearch.length > 0) {
    // Get single row from search result.
    var row = textSearch[0].getRow();    
    // Get the last column so we can use for the row range.
    var rowLastColumn = countryDataSheet.getLastColumn();
    // Get all values for the row.
    var rowValues = countryDataSheet.getRange(row, 1, 1, rowLastColumn).getValues();

    return rowValues[0][1]; // Two-letter ISO code from the second column.
  }
  else {
    return "";
  }
}

When the script is run, the twoLetterIsoCode variable will contain the two-letter ISO code: "AU".

App Script 2: Returning Multiple Row Matches

If we had a dataset that contained multiple matches based on a search term, the script from the first example can be modified using the same fundamental functions. In this case, all we need to do is use a for loop and pass all row values to an array.

The getCountryTwoLetterIsoCode() will look something like this:

function getCountryTwoLetterIsoCode(countryName) {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var countryDataSheet = activeSheet.getSheetByName('Country Data');

  // Find text within sheet.
  var textSearch = countryDataSheet.createTextFinder(countryName).findAll();

  // Array to store all matched rows.
  var searchRows = [];

  if (textSearch.length > 0) {
    // Loop through matches.
    for (var i=0; i < textSearch.length; i++) {
      var row = textSearch[i].getRow();  
      // Get the last column so we can use for the row range.
      var rowLastColumn = countryDataSheet.getLastColumn();
      // Get all values for the row.
      var rowValues = countryDataSheet.getRange(row, 1, 1, rowLastColumn).getValues(); 

      searchRows.push(rowValues);
    }
  }

  return searchRows;
}

The searchRows array will contain a collection of matched rows as well as the column data. To carry out a similar output as shown in the first App Script example - the two-letter country code, the function can be called in the following way:

// Get first match.
var matchedCountryData = getCountryTwoLetterIsoCode("Australia")[0];

// Get the second column value (alpha-2).
var twoLetterIsoCode = matchedCountryData[0][1];

Conclusion

Both examples have demonstrated different ways of returning row values of a search term. The two key lines of code that allows us to do this are:

// Get the last column so we can use for the row range.
var rowLastColumn = countryDataSheet.getLastColumn();

// Get all values for the row.
var rowValues = countryDataSheet.getRange(row, 1, 1, rowLastColumn).getValues();

When building any application, the last thing on any developer's mind is how a build will impact the environment. After all, an application relies on some form of hosting infrastructure - servers, databases, firewalls, switches, routers, cooling systems, etc. The efficiency of how all these pieces of hardware combined are powered to host your application never comes into question.

We are fast becoming aware, more than ever before, that what we do day-to-day has an impact on the environment and are more inclined to take appropriate steps in changing our behaviour to reduce our carbon footprint. However, our behaviour remains unchanged when it comes to our online habits.

Every time a website is visited, a request is made to the server to serve content to the user. This in itself utilises a nominal amount of power for a single user. But when you take hundreds or even thousands of visitors into consideration, the amount of power required builds up exponentially causing more carbon dioxide to be emitted. Of course, this all depends on how efficiently you build your website. For example, reducing unnecessary calls to the database and effective use of caching.

From a digital standpoint, energy is perceived as an infinite commodity with little regard for its carbon footprint.

Interestingly, Microsoft experimented with developing a self-sufficient underwater shipping container-size data centre on the seafloor near Scotland’s Orkney Islands in a two-year trial that ended in 2020. It proved that underwater data centres are feasible, environmentally and economically practical. The consistently cool temperature of the sea allows data centres to be energy-efficient without tapping into freshwater resources. An impressive feat of engineering.

Analysing Site Emissions

I thought it would be a fun exercise to see how my website fairs from an environmental perspective. It's probably not the most ideal time to carry this out as I've only just recently rebuilt my site. But here we go...

There are two websites I am using to analyse how my website fairs from an environment perspective:

• Website Carbon Calculator
• Digital Beacon

These tools are separate entities and use their own algorithms to determine how environmentally friendly a website and even though they both use datasets provided by The Green Web Foundation, it is expected to see differences in the numbers both these tools report.

Website Carbon Calculator

Website Carbon Calculator states my website is 95% cleaner than other web pages tested, produces 0.05kg of CO2 whenever someone visits a page and (most importantly) running on sustainable energy. All good!

The full report can be seen here.

Digital Beacon

Digital Beacon allows me to delve further into more granular stats on how the size of specific page elements has an effect on CO2 emissions on my website, such as JavaScript, images and third-party assets.

This tool has rated my website as "amazing" when it comes to its carbon footprint. The page breakdown report highlights there is still room for improvement in the Script and Image area.

The full report can be seen here.

Examples of Low Carbon Websites

Lowwwcarbon.com showcases low-carbon web design and development. I am hoping, in time, more websites will be submitted and added to their list as great examples that sustainable development doesn't mean you're limited to how you develop websites.

I am proud to have this very website added to the list. It's all the more reason to focus on ensuring my website is climate friendly on an ongoing basis.

Final Thoughts

There are well over 1 billion websites in the world. Just imagine for a moment if even 0.01% of these websites took pre-emptive steps on an ongoing basis to ensure their pages are loading efficiently, this would make quite the difference in combatting CO2 emissions. I'm not stating that this alone will single-handedly combat climate change, but it'll be a start.

Not all hosting companies will have the investment to make their infrastructure environmentally friendly and trial alternatives on a similar scale as Microsoft has done. We as developers need to change our mindset on how we build our applications and have the environmental implications at the forefront of our minds. It's all too easy to develop things out of thin air and see results. The change will have to start at code level.

Further Reading

• Your website is a pollution machine
• 17 ways to make your website more energy efficient
• Static Fantastic