Tiny little script to help you validate LLM responses in Google Sheets

Code.gs

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  // Adds a custom menu to the Google Sheets UI
  ui.createMenu('Checking helper')
    .addItem('Create Sample', 'showStratificationPrompt')
    .addToUi();
}

function showStratificationPrompt() {
  const ui = SpreadsheetApp.getUi();
  // Ask for the column letter for stratification (optional)
  const stratifyResponse = ui.prompt('Stratified Sampling', 'Enter the column letter for grouping (Leave blank if none):', ui.ButtonSet.OK_CANCEL);

  let columnLetter = null;
  if (stratifyResponse.getSelectedButton() == ui.Button.OK && stratifyResponse.getResponseText() !== '') {
    columnLetter = stratifyResponse.getResponseText();
  }
  
  // Ask for the number of rows per sample
  const sampleResponse = ui.prompt('Number of rows', 'How many rows? (per category, if you picked one)', ui.ButtonSet.OK_CANCEL);
  
  if (sampleResponse.getSelectedButton() == ui.Button.OK) {
    const numRows = parseInt(sampleResponse.getResponseText(), 10);
    if (!isNaN(numRows)) {
      createStratifiedSample(columnLetter, numRows);
    } else {
      ui.alert('Invalid number of rows.');
    }
  }
}

function getVisibleData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const filter = sheet.getFilter();
  
  // Check if there is an active filter
  if (filter) {
    // Get all range data
    const range = sheet.getDataRange();
    const values = range.getValues();
    
    // Initialize an array to hold visible rows' data
    let visibleData = [];
    
    // Iterate through each row in the range
    for (let i = 1; i <= range.getLastRow(); i++) {
      // Check if the row is hidden by a filter
      if (!sheet.isRowHiddenByFilter(i)) {
        // If the row is visible, add its data to visibleData
        visibleData.push(values[i-1]); // Adjusting because values is 0-indexed
      }
    }
    
    // Now visibleData contains only the data from rows visible through the filter
    return visibleData;
  } else {
    // If there's no filter, just return all data as before
    const range = sheet.getDataRange();
    return range.getValues();
  }
}

function createStratifiedSample(columnLetter, numRows) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const data = getVisibleData();

  let columnIndex = null;
  if (columnLetter) {
    columnIndex = columnLetter.charCodeAt(0) - 'A'.charCodeAt(0);
  }

  // Stratify data
  const stratifiedData = stratifyData(data, columnIndex, numRows);

  // // Create a new sheet for the sample
  // const resultSheetName = 'Stratified Sample';
  // let resultSheet = ss.getSheetByName(resultSheetName);
  // if (!resultSheet) {
  //   resultSheet = ss.insertSheet(resultSheetName);
  // } else {
  //   resultSheet.clear(); // Clear if it already exists
  // }

  resultSheet = ss.insertSheet();
  var outputRange = resultSheet.getRange(1, 1, stratifiedData.length, stratifiedData[0].length);
  resultSheet.getRange(1, stratifiedData[0].length + 1, 1, 3).setValues([["predicted", "actual", "correctness_note"]]);

  // Output stratified sample to the new sheet
  outputRange.setValues(stratifiedData);

  var styleSourceRange = sheet.getRange(1, 1, stratifiedData.length, stratifiedData[0].length + 3)
  styleSourceRange.copyFormatToRange(resultSheet, 1, stratifiedData[0].length + 3, 1, stratifiedData.length)

  // Pivot table
  pivotSheet = ss.insertSheet();

  const pivotRange = resultSheet.getRange(1, 1, stratifiedData.length, stratifiedData[0].length + 3);
  const pivotTable = pivotSheet.getRange("A1").createPivotTable(pivotRange)

  // predicted
  pivotTable.addRowGroup(stratifiedData[0].length + 1);
  // actual
  pivotTable.addColumnGroup(stratifiedData[0].length + 2);
  // count actuals
  pivotTable.addPivotValue(stratifiedData[0].length + 2, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
  pivotTable.addFilter(stratifiedData[0].length + 2, SpreadsheetApp.newFilterCriteria().whenCellNotEmpty().build())
  ss.setActiveSheet(resultSheet);
}

function stratifyData(data, columnIndex, numRows) {
  // Basic logic to stratify data and generate a random sample from each stratum
  // Assuming the first row is headers and actual data starts from the second row
  const headers = data[0];
  const body = data.slice(1);
  const stratifiedSample = [];
  const strata = {};

  // Stratify data if columnIndex is provided
  if (columnIndex !== null) {
    body.forEach(row => {
      const key = row[columnIndex];
      if (!strata[key]) {
        strata[key] = [];
      }
      strata[key].push(row);
    });

    // Generate a random sample from each stratum
    Object.values(strata).forEach(group => {
      shuffleArray(group); // Randomly shuffle the group
      stratifiedSample.push(...group.slice(0, numRows));
    });
  } else {
    // If no stratification, just randomly sample the data
    shuffleArray(body);
    stratifiedSample.push(...body.slice(0, numRows));
  }

  return [headers, ...stratifiedSample];
}

// Utility function to shuffle an array
function shuffleArray(array) {
  for (let i = array.length - 1; i > 0; i--) {
    const j = Math.floor(Math.random() * (i + 1));
    [array[i], array[j]] = [array[j], array[i]]; // Swap elements
  }
}