function mapInputs(data) {
// Get headers (first row)
const headers = data[0].map(header => header.toString().toLowerCase().trim());
// Remove header row from data
const rows = data.slice(1);
// Initialize the request object
const impactRequest = {
rows: []
};
// Map known column names to API fields
const columnMappings = {
'model_family': 'model_family',
'model_name': 'model_name',
'cloud_id': 'cloud_id',
'cloud_instance_id': 'cloud_instance_id',
'request_time': 'requestTime',
'cloud_region': 'cloud_region',
'country': 'country',
'region': 'region',
'task': 'task',
'input_tokens': 'input_tokens',
'output_tokens': 'output_tokens',
'input_images': 'input_images',
'output_images': 'output_images'
};
// Process each row
rows.forEach(row => {
// Skip empty rows
if (!row || row.every(cell => cell === '' || cell === null || cell === undefined)) {
return;
}
// Create an object to store the transformed row data
const rowData = {
model: {},
node: {}
};
// Map each column value to the appropriate field
headers.forEach((header, index) => {
const value = row[index];
// Skip empty values
if (value === '' || value === null || value === undefined) {
return;
}
// Handle special cases for nested objects
if (header in columnMappings) {
// Map other known fields directly
rowData[columnMappings[header]] = value;
}
// Handle numeric fields
if (header === 'input_tokens' || header === 'output_tokens') {
rowData[columnMappings[header]] = parseInt(value, 10);
}
// Handle arrays
if (header === 'input_images' || header === 'output_images') {
rowData[columnMappings[header]] = value.split(",");
}
});
// Clean up empty objects
if (Object.keys(rowData.model).length === 0) {
delete rowData.model;
}
if (Object.keys(rowData.node).length === 0) {
delete rowData.node;
}
// Add the processed row to the request
if (Object.keys(rowData).length > 0) {
impactRequest.rows.push(rowData);
}
});
return impactRequest;
}
/**
* Model the carbon footprint of AI inferences using the Scope3 API.
* Example usage in Google Sheets:
* =getAICarbonFootprint(
* "your-bearer-token",
* A2:C10 // Range containing headers and rows
* )
*
* @param {string} accessToken - The Bearer token for authentication
* @param {array} inputs - Array of arrays containing header rows that map to the inputs
* @return {array} Array of arrays containing [usage_energy_wh, usage_emissions_gco2e , usage_water_ml, embodied_emissions_gco2e, embodied_water_ml] for each input
* @customfunction
*/
function getAICarbonFootprint(accessToken, inputs) {
const url = 'https://aiapi.scope3.com/impact';
// Validate inputs
if (!inputs) {
throw new Error('All parameters are required');
}
requestData = mapInputs(inputs)
Logger.log(JSON.stringify(requestData));
const options = {
'method': 'post',
'contentType': 'application/json',
'payload': JSON.stringify(requestData),
'headers': {
'Authorization': `Bearer ${accessToken}`,
'Content-Type': 'application/json'
},
'muteHttpExceptions': true
};
try {
const response = UrlFetchApp.fetch(url, options);
const responseCode = response.getResponseCode();
if (responseCode !== 200) {
const errorText = response.getContentText();
throw new Error(`API request failed with status ${responseCode}: ${errorText}`);
}
Logger.log(response.getContentText());
const json = JSON.parse(response.getContentText());
// Return array of results
return json.rows.map(row => [
row.total_impact.usage_energy_wh || 0,
row.total_impact.usage_emissions_gco2e || 0,
row.total_impact.usage_water_ml || 0,
row.total_impact.embodied_emissions_gco2e || 0,
row.total_impact.embodied_water_ml || 0,
]);
} catch (error) {
throw new Error(`API request failed: ${error.message}`);
}
}
/**
* List all models supported by the Scope3 API
* Example usage in Google Sheets:
* =listModels(
* "your-bearer-token",
* "claude" // optional family
* )
*
* @param {string} accessToken - The Bearer token for authentication
* @param {string} family - Family to filter by
* @return {array} Array of arrays containing [model_id, family, model_name] for each input
* @customfunction
*/
function listModels(accessToken, family) {
const url = 'https://aiapi.scope3.com/model';
if (family) {
url += "?family=" + encodeURI(family)
}
const options = {
'method': 'get',
'headers': {
'Authorization': `Bearer ${accessToken}`,
},
'muteHttpExceptions': true
};
try {
const response = UrlFetchApp.fetch(url, options);
const responseCode = response.getResponseCode();
if (responseCode !== 200) {
const errorText = response.getContentText();
throw new Error(`API request failed with status ${responseCode}: ${errorText}`);
}
Logger.log(response.getContentText());
const json = JSON.parse(response.getContentText());
// Return array of results
return json.models.map(row => [
row.id, row.family, row.name
]);
} catch (error) {
throw new Error(`API request failed: ${error.message}`);
}
}
/**
* List all nodes supported by the Scope3 API
* Example usage in Google Sheets:
* =listNodes(
* "your-bearer-token",
* "aws" // optional cloud
* )
*
* @param {string} accessToken - The Bearer token for authentication
* @param {string} cloud - Cloud to filter by
* @return {array} Array of arrays containing [node_id, cloud_id, cloud_instance_id, gpu_id, gpu_count] for each input
* @customfunction
*/
function listNodes(accessToken, cloud) {
const url = 'https://aiapi.scope3.com/node';
if (cloud) {
url += "?cloud=" + encodeURI(cloud)
}
const options = {
'method': 'get',
'headers': {
'Authorization': `Bearer ${accessToken}`,
},
'muteHttpExceptions': true
};
try {
const response = UrlFetchApp.fetch(url, options);
const responseCode = response.getResponseCode();
if (responseCode !== 200) {
const errorText = response.getContentText();
throw new Error(`API request failed with status ${responseCode}: ${errorText}`);
}
Logger.log(response.getContentText());
const json = JSON.parse(response.getContentText());
// Return array of results
return json.nodes.map(row => [
row.id, row.cloud_id, row.cloud_instance_id, row.gpu_id, row.gpu_count
]);
} catch (error) {
throw new Error(`API request failed: ${error.message}`);
}
}