Skip to content

Instantly share code, notes, and snippets.

@penguoir
Last active September 29, 2022 09:29
Show Gist options
  • Save penguoir/e81a97a62ea337610935dac2d97b8957 to your computer and use it in GitHub Desktop.
Save penguoir/e81a97a62ea337610935dac2d97b8957 to your computer and use it in GitHub Desktop.
const token = "YOUR TOKEN HERE"
const url = "https://api.ditchcarbon.com"
/**
* Return Ditch Carbon supplier details
*
* @param {string} supplier - name of the supplier
* @param {string} country - two-letter country code
* @return {array} Ditch Carbon details
* @customfunction
*/
function SUPPLIER(supplier, country) {
const res = UrlFetchApp.fetch(url + "/v1.0/supplier"
+ "?name=" + supplier
+ (country ? "&country=" + country : "") // country, if present.
+ "#refresh", // get google sheets to make request
{
headers: {
"Authorization": "Bearer " + token
}
})
const data = JSON.parse( res.getContentText() );
return [
data.supplier,
data.ef_kg_co2eq,
data.calculation,
data.emissions && data.emissions.scope_1_kg_co2,
data.emissions && data.emissions.scope_2_kg_co2,
data.emissions && data.emissions.scope_3_kg_co2,
data.emissions && data.emissions.total_kg_co2,
data.finances && data.finances.revenue.cents / 100,
data.currency,
data.sources && data.sources.supplier_carbon_emissions_source_url,
data.finances && data.finances.source_url,
data.sources && data.sources.year,
data.sources && data.sources.country,
].join("\t");
}

Usage

  1. In your Google Sheet, select Extensions > Apps Script
  2. Replace all the code in the editor with the code in dc_apps_script.js.
  3. No need to press "Deploy"! Just save the Apps Script by pressing Ctrl-s (or Cmd-s on mac)
  4. Back in your spreadsheet, you can now use the SUPPLIER command in formulae.

Formula:

SUPPLIER(name, [country])

Or, to split the supplier details into columns:

=SPLIT(SUPPLIER("Apple"), CHAR(9), true, false)

Columns:

  • supplier name
  • kg_co2eq
  • direct / indirect
  • scope 1 kg co2e emissions
  • scope 2 kg co2e emissions
  • scope 3 kg co2e emissions
  • total kg co2e emissions
  • revenue
  • currency
  • supplier carbon emissions source url
  • supplier revene source URL
  • year
  • country
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment