Google Sheets

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': 'family',
    'model_name': '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.startsWith('model_')) {
        // Remove 'model_' prefix and map to model object
        const modelField = columnMappings[header] || header.replace('model_', '');
        rowData.model[modelField] = value;
      } else if (header.startsWith('node_')) {
        // Remove 'node_' prefix and map to node object
        const nodeField = header.replace('node_', '');
        rowData.node[nodeField] = value;
      } else 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}`);
  }
}

=getAICarbonFootprint(
  "your-bearer-token",
  "US",
  "h100",
  A2:D10  // Range containing family, model, input_tokens, and output_tokens
)