Script for Google Sheets to track available miniature paints. Extremely unstable and requires specific setup but ‾\_(ツ)_/‾

paint_tracker.js

const SheetNames = { colorsData: 'Colors', availableColors: 'Available' };

const NewColorCell = 'C2';
const PaintTypes = { 'Base': 'B', 'Contrast': 'E', 'Layer': 'H', 'Shade': 'K', 'Dry': 'N', 'Texture': 'Q', 'Technical': 'T', 'Unknown': 'W' };
const FallbackType = 'Unknown';
const CleanColumns = { min: 'B', max: 'X' };
const ListRows = { start: 5, end: 101 };

const EmptyColor = '#FFFFFF';
const FallbackColor = '#FFFFFF';


function test(e) {
  const colorsData = getColorsData();
  
  const availableColorsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SheetNames.availableColors);
  
  const availableColors = getAvailableColors(availableColorsSheet)
  cleanData(availableColorsSheet);
  setData(availableColorsSheet, availableColors, colorsData);
}

function getColorsData() {
  // Parse colors
  let colorsData = {};
  const colorsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SheetNames.colorsData);
  const colorValues = colorsSheet.getRange('A2:D216').getValues();
  for (let i = 0; i < colorValues.length; i++) {
    const colorRow = colorValues[i];
    
    const colorName = colorRow[0];
    const colorHex = colorRow[2];
    const colorType = colorRow[1] ? colorRow[1] : 'Unknown';
    
    if (colorName) {
      colorsData[colorName] = { color: colorHex, type: colorType };
    }
  }
  
  return colorsData;
}

function getAvailableColors(sheet) {
  let availableColors = new Set();
  
  const newColor = sheet.getRange(NewColorCell).getValues()[0][0];
  if (newColor) availableColors.add(newColor);
  
  for (const [paintType, paintColumn] of Object.entries(PaintTypes)) {
    const columnRange = `${paintColumn}${ListRows.start}:${paintColumn}${ListRows.end}`;
    const values = sheet.getRange(columnRange).getValues();
    values.forEach(value => {
      if (value[0])
        availableColors.add(value[0].split('[')[0].trim());
    });
  }

  return availableColors;
}

function cleanData(sheet) {
  const cleanRange = `${CleanColumns.min}${ListRows.start}:${CleanColumns.max}${ListRows.end}`;
  sheet.getRange(cleanRange).setBackground(EmptyColor);
  sheet.getRange(cleanRange).setValue('');
  sheet.getRange(NewColorCell).setValue('');
}

function setData(sheet, colors, colorsData) {
  let currentIndexes = {};
  
  colors.forEach(colorName => {
    const colorData = colorsData[colorName];
    const colorType = colorData ? colorData.type : FallbackType;
    const colorColor = colorData ? colorData.color : FallbackColor;
                 
    const column = colorType in PaintTypes ? PaintTypes[colorType] : PaintTypes[FallbackType];
    const row = colorType in currentIndexes ? currentIndexes[colorType] : ListRows.start;
    currentIndexes[colorType] = row + 1;
     
    const nameCellRange = column + row;         
    sheet.getRange(nameCellRange).setValue(colorName + (colorType in PaintTypes ? '' : ` [${colorType}]`));
  
    const colorCellRange = shiftCharacter(column, 1) + row;           
    sheet.getRange(colorCellRange).setBackground(colorColor);
    if (!colorData || !colorData.color) {      
      sheet.getRange(colorCellRange).setValue('?');
    }
  });
}

function shiftCharacter(baseCharacter, shift) {
  return String.fromCharCode(baseCharacter.charCodeAt(0) + shift) 
}