Google Sheets Integration

Updated 24 Apr 2025

Unlock the power of ScopeGreen's environmental data directly within your spreadsheets! This guide shows you how to integrate the ScopeGreen API with Google Sheets using a simple copy-paste setup for Google Apps Script.

Once configured, you can use a custom function, =SCOPEGREEN(...), just like any built-in Sheets function (e.g., SUM, VLOOKUP), to fetch Life Cycle Assessment (LCA) metrics directly into your cells.

What You'll Need (Prerequisites)

Before you begin, ensure you have the following:

A Google Account: Necessary for accessing Google Sheets and its Apps Script feature.
A ScopeGreen API Key: This key authenticates your requests to the ScopeGreen API.
- Don't have a key? Request access by submitting the appropriate form:
  - Builder Form (For individual developers, researchers, or small projects)
  - Enterprise Form (For business or larger-scale use)
- We typically respond within 24 hours with your key. For more details on authentication, see the Getting Started guide.

Setup Instructions: Adding the SCOPEGREEN function

Follow these steps precisely to add the custom function to your desired Google Sheet:

Open Your Google Sheet: Navigate to the spreadsheet where you want to use the ScopeGreen API.
Access the Script Editor:
- In the top menu, click Extensions.
- Select Apps Script. A new browser tab will open, displaying the script editor environment for your sheet. (Note: This is a generic image showing where Apps Script is typically found.)
Get the Integration Script:
- Open the official scopegreen.js script file in a new tab: ScopeGreen Apps Script on GitHub
- Copy the entire code from that page. The easiest way is usually to click the "Copy raw file" button (often looks like two overlapping squares) located near the top-right of the code display on GitHub.
Paste into Your Editor:
- Return to the Apps Script editor tab you opened in Step 2.
- Delete any placeholder code present (like function myFunction() {}).
- Paste the complete scopegreen.js code you copied.
CRITICAL: Insert Your API Key:
- Scroll through the pasted code to find this specific line:
```
const API_KEY = "your-api-key";
```
- Carefully replace the placeholder "your-api-key" with your actual ScopeGreen API key. Ensure your key remains enclosed in the double quotation marks (").
```
// Correct Example:
const API_KEY = "sgk_live_abcdef1234567890example";
```
- Security Reminder: Your API key provides access to your ScopeGreen account. Anyone who can edit this Google Sheet might also be able to view the script and the key within it. Share responsibly.
Save Your Script Project:
- Click the Save project icon (looks like a floppy disk) in the Apps Script editor toolbar.
- If prompted, give your script project a recognizable name, such as "ScopeGreen API Connector" or "LCA Data Fetcher". Click Rename.
Authorize the Script (One-Time Action):
- The very first time you attempt to use the =SCOPEGREEN() function within any cell in your sheet, Google needs your permission for the script to run.
- An "Authorization Required" dialog will pop up. Click Continue.
- Select the Google account you are currently using for the sheet.
- You will likely encounter a screen titled "Google hasn’t verified this app". This is expected for custom scripts you add yourself. Don't worry; click on "Advanced" (usually a small link at the bottom).
- Then click "Go to [Your Script Name] (unsafe)".
- Google will show you the permissions the script needs:
  - Connect to an external service: Required to call the ScopeGreen API over the internet.
  - View and manage spreadsheets...: Required to read input from cells and write results back into cells.
- Review the permissions and click Allow.

Congratulations! The =SCOPEGREEN() function is now installed, authorized, and ready to use in your Google Sheet.

Using the =SCOPEGREEN() function

You can now call the function from any cell formula.

Function Syntax:

=SCOPEGREEN(itemName, [year], [geography], [metric], [domain], [numMatches], [mode], [notEnglish], [unit])

itemName (String, Required): The primary identifier for the item you want data for (e.g., "steel sheet", "organic cotton"). Must be enclosed in quotes.
Optional Parameters: All subsequent arguments are optional and correspond directly to the query parameters for the /api/metrics/search endpoint.
- Refer to the Core Endpoint documentation or the full API Reference for details on valid values and defaults.
- Remember to enclose text-based parameters (like year, geography, metric, domain, unit) in quotes. Numeric parameters (numMatches) and boolean parameters (notEnglish) do not need quotes.

Usage Examples:

Simplest call (defaults: Carbon footprint, 1 match, etc.):
```
=SCOPEGREEN("concrete block")
```
Using Cell References: If item name is in cell A1:
```
=SCOPEGREEN(A1)
```
Specifying Metric and Geography: Carbon footprint of "glass bottle" in the "EU".
```
=SCOPEGREEN("glass bottle", "", "EU", "Carbon footprint")
```
Requesting Multiple Matches and Unit Conversion: Get 3 matches for "polyester fabric" with the functional unit converted to pounds (lb).
```
=SCOPEGREEN("polyester fabric", "", "", "Carbon footprint", "", 3, "lite", false, "lb")
```

Interpreting the Results

When the =SCOPEGREEN() function executes successfully, it outputs data horizontally across the row, filling adjacent columns to the right.

If you requested numMatches=1 (the default), the output typically spans 9 columns:

Column	Content	Example
1	Match 1: Name	`Match 1: Cotton T-shirt`
2	Match 1: Metric Value	`8.3`
3	Match 1: Metric Unit	`kg CO2 eq / kg`
4	Match 1: Year	`2020`
5	Match 1: Geography	`FR`
6	Match 1: Source Name	`Agribalyse v3.1`
7	Match 1: Source Link	`https://agribalyse.ademe.fr/data`
8	Match 1: Conversion Info	`Unit conversion not requested.`
9	API Explanation	`The top match represents...`

If you request numMatches=2, columns for "Match 2" (Name, Value, Unit, Year, Geography, Source Name, Source Link, Conversion Info) will appear after the first match's data, before the final Explanation column. Similarly for numMatches=3.
Error Handling: If the API encounters an issue (e.g., no match found, invalid input, rate limit), a descriptive error message (like "No good match was found..." or "Rate limit exceeded...") will be displayed directly in the cell where you entered the formula.

Important Notes and Troubleshooting Tips

API Key Accuracy: Double-check that the API key pasted into the script is correct and complete. An invalid key is a common cause of errors.
Authorization: If the function returns #ERROR! or prompts for authorization repeatedly, try re-saving the script and re-authorizing it by running the function again.
Rate Limits & Quotas: Your API key is subject to usage limits (requests per minute, daily/weekly quotas). Excessive use (e.g., hundreds of SCOPEGREEN calls recalculating simultaneously) can lead to temporary 429 Rate limit exceeded errors. Plan your sheet design accordingly.
Execution Timeouts: Google Apps Script has limits on how long a function can run (around 30 seconds for sheet functions). While the script includes a network timeout, very slow API responses could potentially exceed the overall limit. This is rare but possible.
Built-in Caching: The script automatically caches successful results for the exact same set of input parameters for 2 minutes. This significantly speeds up recalculations and reduces API calls if inputs haven't changed.
Viewing Logs for Debugging: If you get unexpected errors, you can inspect detailed logs:
1. Go back to the Apps Script editor (Extensions > Apps Script).
2. In the left sidebar, click Executions.
3. Find the execution entry corresponding to when the error occurred (usually the top one).
4. Look at the Status (e.g., "Completed", "Failed") and click on the entry to view Logger.log messages printed by the script, which can help pinpoint the issue.