Temperature Conversion in Google Apps Script – How to Convert Celsius to Fahrenheit and Vice Versa

Title: “Temperature Conversion in Google Apps Script – How to Convert Celsius to Fahrenheit and Vice Versa”

Description: In this video, we’re exploring a Google Apps Script function that simplifies the conversion of temperatures from Celsius to Fahrenheit and vice versa. Learn how to easily convert temperatures using this script.

Hashtags: #GoogleAppsScript #TemperatureConversion #CelsiusToFahrenheit #FahrenheitToCelsius #CodingTutorial

In the video, you can provide a visual demonstration and explanation of how to use the function to convert temperatures. This can include examples and practical applications of the code in Google Sheets or other Google Workspace apps.

In this example, we’ll create a custom formula that converts temperatures from Celsius to Fahrenheit and vice versa.

Scenario: You want to create a custom formula that converts temperatures between Celsius and Fahrenheit using the conversion formulas:

F=9/5C+32 for Celsius to Fahrenheit, and

C=5/9(F−32) for Fahrenheit to Celsius.

function CONVERT_TEMPERATURES(temperatures,fromUnit,toUnit){

const convertedTemps = [];

for(let i=0;i<temperatures.length;i++){

const temperature = temperatures[i];

let convertedTemperature = 0;

if(fromUnit.toLowerCase() === ‘c’ && toUnit.toLowerCase() === ‘f’){

convertedTemperature = (9/5)*temperature +32;

}else if(fromUnit.toLowerCase() === ‘f’ && toUnit.toLowerCase() === ‘c’){

convertedTemperature = (5/9)*(temperature -32);

}else{

convertedTemperature = temperature;

}

convertedTemps.push([convertedTemperature]);

}

return convertedTemps;

}

This Google Apps Script function, named CONVERT_TEMPERATURES, is designed to convert a list of temperatures from one temperature unit to another. The function takes three parameters:

1. temperatures: An array of temperatures you want to convert.
2. fromUnit: A string representing the source temperature unit, which can be either ‘C’ (Celsius) or ‘F’ (Fahrenheit).
3. toUnit: A string representing the target temperature unit, which can also be either ‘C’ or ‘F’.

Here’s a step-by-step explanation of how the function works:

1. const convertedTemps = []: This initializes an empty array called convertedTemps to store the converted temperatures.
2. The function uses a for loop to iterate over each temperature in the temperatures array:

for (let i = 0; i < temperatures.length; i++) {

  // …

}

3. Inside the loop, it retrieves the current temperature value and stores it in a variable temperature:

const temperature = temperatures[i];

4. It initializes a variable convertedTemperature to 0. This variable will store the converted temperature for the current value of temperature.

let convertedTemperature = 0;

5. The function checks the source and target temperature units using conditional statements. It uses toLowerCase() to make the comparison case-insensitive.

• If fromUnit is ‘C’ (Celsius) and toUnit is ‘F’ (Fahrenheit), it converts the temperature from Celsius to Fahrenheit using the formula: (9/5) * temperature + 32.
• If fromUnit is ‘F’ (Fahrenheit) and toUnit is ‘C’ (Celsius), it converts the temperature from Fahrenheit to Celsius using the formula: (5/9) * (temperature – 32).
• If the source and target units are the same or not recognized, it leaves the temperature unchanged.

6. The converted temperature is added to the convertedTemps array as a single-element array:

convertedTemps.push([convertedTemperature]);

7. After processing all temperatures in the temperatures array, the function returns the convertedTemps array, which contains the converted temperatures.

To use this function, you can call it with an array of temperatures, the source unit, and the target unit, and it will return an array of converted temperatures in the specified target unit. For example:

const temperatures = [0, 100, 25];

const convertedTemperatures = CONVERT_TEMPERATURES(temperatures, ‘C’, ‘F’);

The convertedTemperatures variable will contain an array of temperatures converted from Celsius to Fahrenheit.

Data Table:

A	B	C	D
Temperature	Celsius	Fahrenheit	Converted
25
70
0
Total

Step 1: Setting up the Spreadsheet

• Create a new Google Sheets document.
• Enter your temperature values in column A starting from row 2.
• Leave cells in columns B, C, and D empty for now.
• In cell B1, enter “Celsius” as a header.
• In cell C1, enter “Fahrenheit” as a header.
• In cell D1, enter “Converted” as a header.

Step 2: Writing the Google Apps Script Code

• Click on “Extensions” in the top menu, then select “Apps Script”.
• Delete any default code and replace it with the following script:

// Custom formula to convert temperatures between Celsius and Fahrenheit

function CONVERT_TEMPERATURES(temperatures, fromUnit, toUnit) {

  var convertedTemps = [];

  for (var i = 0; i < temperatures.length; i++) {

    var temperature = temperatures[i];

    var convertedTemperature = 0;

    if (fromUnit.toLowerCase() === “c” && toUnit.toLowerCase() === “f”) {

      convertedTemperature = (9 / 5) * temperature + 32;

    } else if (fromUnit.toLowerCase() === “f” && toUnit.toLowerCase() === “c”) {

      convertedTemperature = (5 / 9) * (temperature – 32);

    } else {

      convertedTemperature = temperature; // No conversion needed

    }

    convertedTemps.push([convertedTemperature]);

  }

  return convertedTemps;

}

Step 3: Using the Custom Formula in Google Sheets

• Go back to your Google Sheets document.
• In cell B2, enter the following formula:

=CONVERT_TEMPERATURES(A2:A, “C”, “F”)

• In cell C2, enter the following formula:

=CONVERT_TEMPERATURES(A2:A, “F”, “C”)

Explanation of the Code:

• The function CONVERT_TEMPERATURES converts temperatures between Celsius and Fahrenheit (and vice versa).
• It takes three parameters: temperatures, fromUnit, and toUnit.
  • temperatures: The range of cells containing the temperature values.
  • fromUnit: The unit of the temperature to convert from (“C” for Celsius, “F” for Fahrenheit).
  • toUnit: The unit of the temperature to convert to (“C” for Celsius, “F” for Fahrenheit).
• Inside the function, a loop iterates through each temperature value in the range.
• Depending on the conversion direction (from Celsius to Fahrenheit or vice versa), the appropriate conversion formula is applied.
• The converted temperature values are stored in an array (convertedTemps) for each input value.
• The function returns the array of converted temperatures.

Step 4: Testing the Custom Formula

• Enter temperature values in column A starting from row 2.
• Use the custom formulas in cells B2 and C2 to convert the temperatures from Celsius to Fahrenheit and from Fahrenheit to Celsius, respectively.

For example, if you have entered the temperature values in column A, the corresponding converted temperatures in Celsius (in column C) and Fahrenheit (in column B) should be displayed.

Remember to enable the “Google Apps Script” extension and use the exact function name (CONVERT_TEMPERATURES) in your formulas.