Google Apps Script
Generated from the same taxonomy and scoring model previewed here — 9 families, 52 innovations, 213 subtypes. Adjust the model in this app, re-copy, and paste into the Sheet.
Install
Extensions → Apps Script → paste as Code.gs → save → reload the Sheet
- Open a blank Google Sheet, then Extensions → Apps Script.
- Delete the placeholder code, paste this file, save.
- Reload the Sheet. A new “Innovation Menu” appears.
- Run 1. Build / Reset Workbook and approve the permission prompt.
- Fill Brand Setup, then run 2. Add Brand Row to fan out the entry rows for those brands.
- Set statuses in Adoption Entry and answers in Checklists, then run 3. Refresh Scores & Dashboards.
Code.gs
/**
* INNOVATION ADOPTION MATURITY — Paid Search
* Generated by the Lovable prototype. Paste into Extensions > Apps Script,
* save, reload the Sheet, then run: Innovation Menu > 1. Build / Reset Workbook
*
* Workflow:
* Taxonomy (reference) -> Brand Setup (manual) -> Adoption Entry (manual)
* -> Checklists (manual) -> Scores (calc) -> Exec Heat Map / Family Matrix
* / Channel Detail (dashboards)
*/
// ---------------------------------------------------------------- CONFIG
var FAMILIES = [{"id":"ai-search-automation","name":"AI Search & Automation","short":"AI & Automation"},{"id":"search-optimization","name":"Search Optimization","short":"Search Optim."},{"id":"measurement-analytics","name":"Measurement & Analytics","short":"Measurement"},{"id":"video-visual-discovery","name":"Video & Visual Discovery","short":"Video"},{"id":"emerging-search-discovery-platforms","name":"Emerging Search & Discovery Platforms","short":"Emerging"},{"id":"seo-geo-ai-visibility","name":"SEO / GEO / AI Visibility","short":"SEO / GEO"},{"id":"audience-intelligence-targeting","name":"Audience Intelligence & Targeting","short":"Audience"},{"id":"cross-functional-optimizations","name":"Cross-Functional Optimizations","short":"Cross-Channel"},{"id":"operational-excellence-automation","name":"Operational Excellence & Automation","short":"Ops Excellence"}];
var TAXONOMY = [["AI Search & Automation","Google","AI Max","Search Term Matching"],["AI Search & Automation","Google","AI Max","Text Customization"],["AI Search & Automation","Google","AI Max","Final URL Expansion"],["AI Search & Automation","Google","AI Max","Brand Controls"],["AI Search & Automation","Google","AI Max","URL Exclusions"],["AI Search & Automation","Google","AI Max","Negative Keyword Controls"],["AI Search & Automation","Google","Broad Match","Intent Expansion"],["AI Search & Automation","Google","Broad Match","Long Tail Capture"],["AI Search & Automation","Google","VBB","Adobe Weighted Actions"],["AI Search & Automation","Google","VBB","Floodlight Weighted Actions"],["AI Search & Automation","Google","VBB","Crossix Weighted Actions"],["AI Search & Automation","Google","VBB","CRM Weighted Actions"],["AI Search & Automation","Google","VBB","NPI Weighted Actions"],["AI Search & Automation","Google","VBB","Offline Conversion Import"],["AI Search & Automation","Google","SA360 Enterprise Bidding","Portfolio Bidding"],["AI Search & Automation","Google","SA360 Enterprise Bidding","Budget Bid Strategy"],["AI Search & Automation","Google","SA360 Enterprise Bidding","Cross Engine Optimization"],["AI Search & Automation","Google","SA360 Enterprise Bidding","Portfolio Experiments"],["AI Search & Automation","Google","AI Search Surfaces","AI Mode Ads"],["AI Search & Automation","Google","AI Search Surfaces","AI Overview Ads"],["AI Search & Automation","Google","Performance Max","Search Complement"],["AI Search & Automation","Microsoft","Search Automation","Broad Match"],["AI Search & Automation","Microsoft","Search Automation","Dynamic Search Ads"],["AI Search & Automation","Microsoft","Search Automation","Responsive Search Ads"],["AI Search & Automation","Microsoft","Search Automation","Search Intent Expansion"],["AI Search & Automation","Microsoft","Automated Bidding","Maximize Conversions"],["AI Search & Automation","Microsoft","Automated Bidding","Maximize Conversion Value"],["AI Search & Automation","Microsoft","Automated Bidding","Target CPA"],["AI Search & Automation","Microsoft","Automated Bidding","Target ROAS"],["AI Search & Automation","Microsoft","Automated Bidding","Target Impression Share"],["AI Search & Automation","Microsoft","Automated Bidding","Enhanced CPC"],["AI Search & Automation","Microsoft","Value-Based Optimization","Revenue Imports"],["AI Search & Automation","Microsoft","Value-Based Optimization","Offline Conversion Imports"],["AI Search & Automation","Microsoft","Value-Based Optimization","UET-Based Optimization"],["AI Search & Automation","Microsoft","Value-Based Optimization","CRM-Based Optimization"],["AI Search & Automation","Microsoft","Microsoft Performance Max","PMax Testing"],["AI Search & Automation","Microsoft","Copilot Search","Copilot Ads"],["AI Search & Automation","Microsoft","Copilot Search","Copilot Sponsored Responses"],["AI Search & Automation","Microsoft","Copilot Search","Copilot Search Experiences"],["Search Optimization","Keyword Expansion","Keyword Expansion","Symptom Keywords"],["Search Optimization","Keyword Expansion","Keyword Expansion","Condition Keywords"],["Search Optimization","Keyword Expansion","Keyword Expansion","Treatment Keywords"],["Search Optimization","Keyword Expansion","Keyword Expansion","Brand Keywords"],["Search Optimization","Keyword Expansion","Keyword Expansion","Competitive Keywords"],["Search Optimization","Search Query Mining","Search Query Mining","Query Reviews"],["Search Optimization","Search Query Mining","Search Query Mining","Negative Development"],["Search Optimization","Search Query Mining","Search Query Mining","Query Categorization"],["Search Optimization","Campaign Optimization","Campaign Optimization","RSA Optimization"],["Search Optimization","Campaign Optimization","Campaign Optimization","Landing Page Mapping"],["Search Optimization","Campaign Optimization","Campaign Optimization","Geo Optimization"],["Search Optimization","Campaign Optimization","Campaign Optimization","Device Optimization"],["Search Optimization","Campaign Optimization","Campaign Optimization","Dayparting"],["Search Optimization","Campaign Optimization","Campaign Optimization","Budget Reallocation"],["Search Optimization","Audience Optimization","Google","Customer Match"],["Search Optimization","Audience Optimization","Google","Remarketing"],["Search Optimization","Audience Optimization","Google","Observation Mode"],["Search Optimization","Audience Optimization","Microsoft","Customer Match"],["Search Optimization","Audience Optimization","Microsoft","Remarketing Lists"],["Search Optimization","Audience Optimization","Microsoft","In-Market Audiences"],["Search Optimization","Audience Optimization","LinkedIn Integration","Company Targeting"],["Search Optimization","Audience Optimization","LinkedIn Integration","Industry Targeting"],["Search Optimization","Audience Optimization","LinkedIn Integration","Job Function Targeting"],["Measurement & Analytics","Attribution","Attribution","Adobe Conversion"],["Measurement & Analytics","Attribution","Attribution","Floodlight"],["Measurement & Analytics","Attribution","Attribution","Crossix"],["Measurement & Analytics","Attribution","Attribution","CRM"],["Measurement & Analytics","Attribution","Attribution","Offline Conversion Import"],["Measurement & Analytics","Attribution","Attribution","PLD"],["Measurement & Analytics","Microsoft Measurement","UET","Standard UET"],["Measurement & Analytics","Microsoft Measurement","UET","Enhanced Conversions"],["Measurement & Analytics","Microsoft Measurement","UET","Revenue Imports"],["Measurement & Analytics","Microsoft Measurement","UET","Offline Conversions"],["Measurement & Analytics","Microsoft Measurement","UET","Remarketing Lists"],["Measurement & Analytics","Microsoft Measurement","Clarity","Heatmaps"],["Measurement & Analytics","Microsoft Measurement","Clarity","Session Replay"],["Measurement & Analytics","Microsoft Measurement","Clarity","Scroll Depth"],["Measurement & Analytics","Microsoft Measurement","Clarity","AI Summaries"],["Measurement & Analytics","Microsoft Measurement","Clarity","Landing Page Diagnostics"],["Measurement & Analytics","Infrastructure","Infrastructure","Tag Audits"],["Measurement & Analytics","Infrastructure","Infrastructure","Pixel QA"],["Measurement & Analytics","Infrastructure","Infrastructure","Server Side Tagging"],["Measurement & Analytics","Infrastructure","Infrastructure","AEM Migration Readiness"],["Measurement & Analytics","Reporting","Reporting","Looker Studio"],["Measurement & Analytics","Reporting","Reporting","Power BI"],["Measurement & Analytics","Reporting","Reporting","BI Reporting"],["Measurement & Analytics","Reporting","Reporting","Dashboard Automation"],["Video & Visual Discovery","YouTube","YouTube","Pre-Roll"],["Video & Visual Discovery","YouTube","YouTube","Discovery"],["Video & Visual Discovery","YouTube","YouTube","Shorts"],["Video & Visual Discovery","YouTube","YouTube","Video Reach Campaigns"],["Video & Visual Discovery","YouTube","YouTube","Video Action Campaigns"],["Video & Visual Discovery","YouTube","YouTube","Bumper Ads"],["Video & Visual Discovery","Demand Gen","Demand Gen","DTC Demand Gen"],["Video & Visual Discovery","Demand Gen","Demand Gen","HCP Demand Gen"],["Video & Visual Discovery","Demand Gen","Demand Gen","Customer Match Activation"],["Video & Visual Discovery","MSAN","Inventory","Audience Ads"],["Video & Visual Discovery","MSAN","Inventory","Native Ads"],["Video & Visual Discovery","MSAN","Inventory","Display Ads"],["Video & Visual Discovery","MSAN","Inventory","Video Ads"],["Video & Visual Discovery","MSAN","Inventory","Multimedia Ads"],["Video & Visual Discovery","MSAN","Audiences","Dynamic Remarketing"],["Video & Visual Discovery","MSAN","Audiences","In Market Audiences"],["Video & Visual Discovery","MSAN","Audiences","Custom Audiences"],["Video & Visual Discovery","MSAN","Audiences","Predictive Audiences"],["Video & Visual Discovery","MSAN","Audiences","Customer Match"],["Video & Visual Discovery","MSAN","LinkedIn","Company Targeting"],["Video & Visual Discovery","MSAN","LinkedIn","Industry Targeting"],["Video & Visual Discovery","MSAN","LinkedIn","Job Function Targeting"],["Video & Visual Discovery","MSAN","Microsoft Properties","Edge Inventory"],["Video & Visual Discovery","MSAN","Microsoft Properties","Outlook Inventory"],["Video & Visual Discovery","MSAN","Microsoft Properties","Casual Games Inventory"],["Emerging Search & Discovery Platforms","OpenEvidence","OpenEvidence","Direct Match"],["Emerging Search & Discovery Platforms","OpenEvidence","OpenEvidence","Intent Match"],["Emerging Search & Discovery Platforms","OpenEvidence","OpenEvidence","Multi Match"],["Emerging Search & Discovery Platforms","OpenEvidence","OpenEvidence","Specialty Targeting"],["Emerging Search & Discovery Platforms","OpenEvidence","OpenEvidence","NPI Targeting"],["Emerging Search & Discovery Platforms","OpenEvidence","OpenEvidence","Display"],["Emerging Search & Discovery Platforms","OpenEvidence","OpenEvidence","Video"],["Emerging Search & Discovery Platforms","OpenEvidence","OpenEvidence","Rich Media"],["Emerging Search & Discovery Platforms","OpenEvidence","OpenEvidence","PLD Reporting"],["Emerging Search & Discovery Platforms","OpenEvidence","OpenEvidence","Competitive Queries"],["Emerging Search & Discovery Platforms","OpenEvidence","OpenEvidence","SOV"],["Emerging Search & Discovery Platforms","OpenEvidence","OpenEvidence","Search Query Insights"],["Emerging Search & Discovery Platforms","OpenEvidence","OpenEvidence","ROI Analysis"],["Emerging Search & Discovery Platforms","TrendMD","TrendMD","Sponsored Articles"],["Emerging Search & Discovery Platforms","TrendMD","TrendMD","Journal Placement"],["Emerging Search & Discovery Platforms","TrendMD","TrendMD","PLD Promotion"],["Emerging Search & Discovery Platforms","Perform Media","Perform Media","Native Content"],["Emerging Search & Discovery Platforms","Perform Media","Perform Media","Sponsored Education"],["Emerging Search & Discovery Platforms","Perform Media","Perform Media","Audience Extension"],["Emerging Search & Discovery Platforms","PulsePoint","PulsePoint","Search Extensions"],["Emerging Search & Discovery Platforms","PulsePoint","PulsePoint","HCP365"],["Emerging Search & Discovery Platforms","PulsePoint","PulsePoint","Contextual HCP"],["Emerging Search & Discovery Platforms","Healio AI","Healio AI","AI Search Ads"],["Emerging Search & Discovery Platforms","Healio AI","Healio AI","Contextual Targeting"],["Emerging Search & Discovery Platforms","Ostro","Ostro","Website Assistant"],["Emerging Search & Discovery Platforms","Ostro","Ostro","Patient Education"],["Emerging Search & Discovery Platforms","Ostro","Ostro","Guided Journeys"],["Emerging Search & Discovery Platforms","ChatGPT","ChatGPT","Sponsored Responses"],["Emerging Search & Discovery Platforms","ChatGPT","ChatGPT","Reminder Ads"],["Emerging Search & Discovery Platforms","ChatGPT","ChatGPT","Conversation Ads"],["Emerging Search & Discovery Platforms","ChatGPT","ChatGPT","Pixel Tracking"],["SEO / GEO / AI Visibility","GEO","GEO","AI Visibility"],["SEO / GEO / AI Visibility","GEO","GEO","Prompt Monitoring"],["SEO / GEO / AI Visibility","GEO","GEO","Citation Tracking"],["SEO / GEO / AI Visibility","GEO","GEO","AI Search Presence"],["SEO / GEO / AI Visibility","SEO","SEO","Technical SEO"],["SEO / GEO / AI Visibility","SEO","SEO","Core Web Vitals"],["SEO / GEO / AI Visibility","SEO","SEO","Content Optimization"],["SEO / GEO / AI Visibility","SEO","SEO","Query Mapping"],["SEO / GEO / AI Visibility","Competitive Visibility","Competitive Visibility","SEMRush"],["SEO / GEO / AI Visibility","Competitive Visibility","Competitive Visibility","Ahrefs"],["SEO / GEO / AI Visibility","Competitive Visibility","Competitive Visibility","Profound"],["SEO / GEO / AI Visibility","Competitive Visibility","Competitive Visibility","AI Brand Snapshot"],["SEO / GEO / AI Visibility","Competitive Visibility","Competitive Visibility","Domain Intelligence"],["SEO / GEO / AI Visibility","Competitive Visibility","Competitive Visibility","LLM Brand Insights"],["SEO / GEO / AI Visibility","Diagnostics","Diagnostics","Google Search Console"],["SEO / GEO / AI Visibility","Diagnostics","Diagnostics","Bing Webmaster Tools"],["Audience Intelligence & Targeting","Audience Expansion","Audience Expansion","AIM XR"],["Audience Intelligence & Targeting","Audience Expansion","Audience Expansion","IQVIA Audiences"],["Audience Intelligence & Targeting","Audience Expansion","Audience Expansion","Verified HCP Lists"],["Audience Intelligence & Targeting","Audience Expansion","Audience Expansion","NPI Segments"],["Audience Intelligence & Targeting","Audience Alignment","Audience Alignment","Unified Audience Strategy"],["Audience Intelligence & Targeting","Audience Alignment","Audience Alignment","Cross Channel Mapping"],["Audience Intelligence & Targeting","Audience Alignment","Audience Alignment","Audience Gap Analysis"],["Audience Intelligence & Targeting","Retargeting","Retargeting","Search to Social"],["Audience Intelligence & Targeting","Retargeting","Retargeting","Search to Programmatic"],["Audience Intelligence & Targeting","Retargeting","Retargeting","Social to Search"],["Audience Intelligence & Targeting","Retargeting","Retargeting","Cross Channel Sequencing"],["Cross-Functional Optimizations","Search + Social","Search + Social","Keyword Sharing"],["Cross-Functional Optimizations","Search + Social","Search + Social","Search Retargeting"],["Cross-Functional Optimizations","Search + Social","Search + Social","Social Search Insights"],["Cross-Functional Optimizations","Search + Social","Search + Social","Reddit Keyword Reports"],["Cross-Functional Optimizations","Search + Social","Search + Social","TikTok Keyword Insights"],["Cross-Functional Optimizations","Search + Social","Search + Social","Pinterest Trends"],["Cross-Functional Optimizations","Search + SEO / GEO","Search + SEO / GEO","Query Mapping"],["Cross-Functional Optimizations","Search + SEO / GEO","Search + SEO / GEO","Landing Page Alignment"],["Cross-Functional Optimizations","Search + SEO / GEO","Search + SEO / GEO","Quality Score Reviews"],["Cross-Functional Optimizations","Search + SEO / GEO","Search + SEO / GEO","Content Recommendations"],["Cross-Functional Optimizations","Search + SEO / GEO","Search + SEO / GEO","AI Visibility Reviews"],["Cross-Functional Optimizations","Search + Programmatic","Search + Programmatic","PulsePoint Extensions"],["Cross-Functional Optimizations","Search + Programmatic","Search + Programmatic","Evertune Activations"],["Cross-Functional Optimizations","Search + Programmatic","Search + Programmatic","Sequential Messaging"],["Cross-Functional Optimizations","Search + Programmatic","Search + Programmatic","Audience Sharing"],["Cross-Functional Optimizations","Search + Audience Intelligence","Search + Audience Intelligence","Social Intelligence Insights"],["Cross-Functional Optimizations","Search + Audience Intelligence","Search + Audience Intelligence","Sentiment Analysis"],["Cross-Functional Optimizations","Search + Audience Intelligence","Search + Audience Intelligence","Conference Listening"],["Cross-Functional Optimizations","Search + Audience Intelligence","Search + Audience Intelligence","Theme Mining"],["Cross-Functional Optimizations","Strategic Moments","Strategic Moments","Tentpole Event Heavy-Up"],["Cross-Functional Optimizations","Strategic Moments","Strategic Moments","Launch Halo Support"],["Cross-Functional Optimizations","Strategic Moments","Strategic Moments","Conference / Congress Activations"],["Cross-Functional Optimizations","Strategic Moments","Strategic Moments","Awareness Month Activations"],["Cross-Functional Optimizations","Creative Integration","Creative Integration","Vertical Video Alignment"],["Cross-Functional Optimizations","Creative Integration","Creative Integration","TikTok / Reels → Shorts"],["Cross-Functional Optimizations","Creative Integration","Creative Integration","Search-Informed Social Messaging"],["Cross-Functional Optimizations","Creative Integration","Creative Integration","Social-Informed Search Copy"],["Cross-Functional Optimizations","Creative Integration","Creative Integration","Touchstone Analysis"],["Operational Excellence & Automation","WPP Open","WPP Open","Campaign Diagnostics"],["Operational Excellence & Automation","WPP Open","WPP Open","Prompt Libraries"],["Operational Excellence & Automation","WPP Open","WPP Open","Dashboard Agents"],["Operational Excellence & Automation","WPP Open","WPP Open","Reporting Agents"],["Operational Excellence & Automation","AI Productivity","AI Productivity","Copilot"],["Operational Excellence & Automation","AI Productivity","AI Productivity","ChatGPT"],["Operational Excellence & Automation","AI Productivity","AI Productivity","Gemini"],["Operational Excellence & Automation","AI Productivity","AI Productivity","Claude"],["Operational Excellence & Automation","Automation","Automation","QA Automation"],["Operational Excellence & Automation","Automation","Automation","Search Query Automation"],["Operational Excellence & Automation","Automation","Automation","Reporting Automation"],["Operational Excellence & Automation","Automation","Automation","Budget Monitoring"],["Operational Excellence & Automation","Enablement","Enablement","Playbooks"],["Operational Excellence & Automation","Enablement","Enablement","Training Programs"],["Operational Excellence & Automation","Enablement","Enablement","Case Studies"],["Operational Excellence & Automation","Enablement","Enablement","POV Development"]];
var LEVELS = [{"label":"Not Applicable","value":null},{"label":"Not Started","value":0},{"label":"Planned","value":1},{"label":"Piloting","value":2},{"label":"Adopted","value":3},{"label":"Scaled & Optimized","value":4}];
var RELEVANCE = [{"label":"Core Priority","weight":1},{"label":"Secondary","weight":0.6},{"label":"Watch Only","weight":0.25},{"label":"Out of Scope","weight":0}];
var BUDGET_TIERS = [{"label":"Tier 1 — Investment Brand","ambition":1},{"label":"Tier 2 — Growth Brand","ambition":0.8},{"label":"Tier 3 — Maintenance Brand","ambition":0.55},{"label":"Tier 4 — Minimal / LOE","ambition":0.35}];
var PILLARS = [{"id":"optimization","name":"Optimization Efficiency","weight":0.2,"items":["Weekly search query mining with documented negative development","Budget pacing reviewed and reallocated at least weekly","RSA asset ratings reviewed monthly with refresh cadence","Bid strategy targets re-based against seasonality / conversion lag","Landing page mapping validated against top converting queries","Geo, device and daypart modifiers reviewed each quarter","Test-and-learn roadmap active with documented hypotheses","QA automation or checklist run before every launch","Conversion tagging audited at least twice a year","Wasted-spend / non-converting query rate trending down"]},{"id":"crossChannel","name":"Cross-Channel Collaboration","weight":0.15,"items":["Shared keyword / query insights delivered to social and programmatic","Search informs SEO and GEO content recommendations","Audience segments mapped and shared across channels","Sequential messaging live between search and at least one channel","Joint planning sessions held with channel leads each quarter","Unified measurement view across paid search and adjacent media","Creative alignment between search copy and social / video messaging","Shared tentpole and congress activation calendar"]},{"id":"strategic","name":"Strategic Relevancy","weight":0.15,"items":["Innovation roadmap tied to brand business objectives","Client stakeholder has approved the innovation ambition level","Budget explicitly allocated to test-and-learn","MLR / regulatory pathway understood for planned innovations","Adoption decisions documented with rationale (including opt-outs)","Competitive benchmark reviewed within the last two quarters","Measurable business impact captured for adopted innovations"]}];
var ADOPTION_WEIGHT = 0.5;
var MAX_LEVEL = 4;
var TIERS = [{"key":"LEADING","label":"Leading","min":80},{"key":"ADVANCING","label":"Advancing","min":60},{"key":"BUILDING","label":"Building","min":40},{"key":"EMERGING","label":"Emerging","min":20},{"key":"NASCENT","label":"Nascent","min":0}];
var TABS = {
readme: 'README',
taxonomy: 'Taxonomy',
setup: 'Brand Setup',
entry: 'Adoption Entry',
checks: 'Checklists',
scores: 'Scores',
exec: 'Exec Heat Map',
matrix: 'Family Matrix',
channel: 'Channel Detail'
};
var BANDS = {
advanced: ['#d6f0dd', '#1e6b3a'],
building: ['#dbe7fb', '#1f3f7a'],
planned: ['#fbf0cf', '#7a5c14'],
early: ['#fde2cf', '#8a4415'],
blocked: ['#fbd8db', '#8c1f2b'],
na: ['#eeeeee', '#777777']
};
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Innovation Menu')
.addItem('1. Build / Reset Workbook', 'buildWorkbook')
.addItem('2. Add Brand Row', 'addBrandRow')
.addItem('3. Refresh Scores & Dashboards', 'refreshAll')
.addToUi();
}
// ---------------------------------------------------------------- HELPERS
function ss() { return SpreadsheetApp.getActiveSpreadsheet(); }
function sheet(name, clear) {
var s = ss().getSheetByName(name);
if (!s) s = ss().insertSheet(name);
if (clear) { s.clear(); s.clearConditionalFormatRules(); }
return s;
}
function header(s, values, row) {
row = row || 1;
s.getRange(row, 1, 1, values.length).setValues([values])
.setBackground('#1f2a44').setFontColor('#ffffff').setFontWeight('bold')
.setVerticalAlignment('middle');
s.setFrozenRows(row);
}
function labels(arr, key) { return arr.map(function (x) { return x[key || 'label']; }); }
function dropdown(s, range, list) {
var rule = SpreadsheetApp.newDataValidation().requireValueInList(list, true)
.setAllowInvalid(false).build();
range.setDataValidation(rule);
}
function brandKey(row) { return row[0] + ' — ' + row[2]; } // Brand — Audience
function innovationList() {
var seen = {}, out = [];
TAXONOMY.forEach(function (r) {
var k = r[0] + '|' + r[2];
if (!seen[k]) { seen[k] = true; out.push({ family: r[0], group: r[1], name: r[2] }); }
});
return out;
}
// ---------------------------------------------------------------- BUILD
function buildWorkbook() {
buildReadme();
buildTaxonomy();
buildSetup();
buildEntry();
buildChecklists();
refreshAll();
ss().setActiveSheet(ss().getSheetByName(TABS.setup));
SpreadsheetApp.getUi().alert('Workbook ready. Fill Brand Setup, then Adoption Entry + Checklists, then run "3. Refresh Scores & Dashboards".');
}
function buildReadme() {
var s = sheet(TABS.readme, true);
var rows = [
['INNOVATION ADOPTION MATURITY — PAID SEARCH'],
[''],
['HOW TO USE'],
['1. Brand Setup — one row per brand/audience. Set budget tier and family relevance (Core / Secondary / Watch / Out of Scope).'],
['2. Adoption Entry — one row per brand x innovation. Pick a status from the dropdown only. Nothing else to type.'],
['3. Checklists — answer Yes / Partial / No for optimization, cross-channel and strategic questions.'],
['4. Run Innovation Menu > Refresh Scores & Dashboards.'],
[''],
['SCORING'],
['Adoption Maturity = SUM(level x relevance weight) / SUM(4 x relevance weight) across in-scope innovations.'],
['Out-of-Scope families leave the denominator, so a brand is never punished for innovation it has no budget or strategic need for.'],
['Every brand is still reported on the same 0-100 scale.'],
['Composite = 50% adoption + 20% optimization efficiency + 15% cross-channel collaboration + 15% strategic relevancy.'],
['Scope Coverage shows how much of the full innovation universe the brand plays in — read it next to the score.'],
['Ambition Index compares the score to the expectation set by budget tier (100 = meeting the bar for that tier).']
];
s.getRange(1, 1, rows.length, 1).setValues(rows);
s.getRange(1, 1).setFontSize(14).setFontWeight('bold');
s.getRange(3, 1).setFontWeight('bold');
s.getRange(9, 1).setFontWeight('bold');
s.setColumnWidth(1, 900);
}
function buildTaxonomy() {
var s = sheet(TABS.taxonomy, true);
header(s, ['Innovation Family', 'Group / Partner', 'Innovation', 'Subtype']);
s.getRange(2, 1, TAXONOMY.length, 4).setValues(TAXONOMY);
s.autoResizeColumns(1, 4);
}
function buildSetup() {
var s = sheet(TABS.setup, true);
var head = ['Brand', 'Website', 'Audience', 'Budget Tier', 'Lead']
.concat(FAMILIES.map(function (f) { return 'Relevance: ' + f.short; }));
header(s, head);
var rows = 40;
dropdown(s, s.getRange(2, 3, rows, 1), ['DTC', 'HCP', 'Both']);
dropdown(s, s.getRange(2, 4, rows, 1), labels(BUDGET_TIERS));
dropdown(s, s.getRange(2, 6, rows, FAMILIES.length), labels(RELEVANCE));
s.getRange(2, 6, rows, FAMILIES.length).setValue('Core Priority');
s.setColumnWidth(1, 150); s.setColumnWidth(2, 160); s.setColumnWidth(4, 190);
s.getRange(1, 1, 1, head.length).setWrap(true);
}
function buildEntry() {
var s = sheet(TABS.entry, true);
header(s, ['Brand — Audience', 'Innovation Family', 'Group / Partner', 'Innovation', 'Status', 'Owner', 'Note']);
var brands = getBrands();
var invs = innovationList();
var rows = [];
brands.forEach(function (b) {
invs.forEach(function (i) { rows.push([b.key, i.family, i.group, i.name, 'Not Started', '', '']); });
});
if (!rows.length) rows.push(['(add brands in Brand Setup first)', '', '', '', '', '', '']);
s.getRange(2, 1, rows.length, 7).setValues(rows);
dropdown(s, s.getRange(2, 5, Math.max(rows.length, 1), 1), labels(LEVELS));
colorByBand(s, 5, rows.length);
s.autoResizeColumns(1, 4);
s.setColumnWidth(5, 170);
}
function buildChecklists() {
var s = sheet(TABS.checks, true);
header(s, ['Brand — Audience', 'Pillar', 'Checklist Item', 'Answer', 'Evidence / Note']);
var brands = getBrands();
var rows = [];
brands.forEach(function (b) {
PILLARS.forEach(function (p) {
p.items.forEach(function (item) { rows.push([b.key, p.name, item, 'No', '']); });
});
});
if (!rows.length) rows.push(['(add brands in Brand Setup first)', '', '', '', '']);
s.getRange(2, 1, rows.length, 5).setValues(rows);
dropdown(s, s.getRange(2, 4, Math.max(rows.length, 1), 1), ['Yes', 'Partial', 'No', 'Not Applicable']);
s.setColumnWidth(3, 520);
}
// ---------------------------------------------------------------- READ
function getBrands() {
var s = ss().getSheetByName(TABS.setup);
if (!s) return [];
var last = s.getLastRow();
if (last < 2) return [];
var vals = s.getRange(2, 1, last - 1, 5 + FAMILIES.length).getValues();
var out = [];
vals.forEach(function (r) {
if (!r[0]) return;
var rel = {};
FAMILIES.forEach(function (f, i) { rel[f.name] = r[5 + i] || 'Core Priority'; });
out.push({
key: brandKey(r), brand: r[0], website: r[1], audience: r[2],
tier: r[3] || BUDGET_TIERS[0].label, lead: r[4], relevance: rel
});
});
return out;
}
function weightFor(label) {
for (var i = 0; i < RELEVANCE.length; i++) if (RELEVANCE[i].label === label) return RELEVANCE[i].weight;
return 1;
}
function levelFor(label) {
for (var i = 0; i < LEVELS.length; i++) if (LEVELS[i].label === label) return LEVELS[i].value;
return 0;
}
function ambitionFor(label) {
for (var i = 0; i < BUDGET_TIERS.length; i++) if (BUDGET_TIERS[i].label === label) return BUDGET_TIERS[i].ambition;
return 1;
}
function answerValue(label) {
if (label === 'Yes') return 1;
if (label === 'Partial') return 0.5;
if (label === 'Not Applicable') return null;
return 0;
}
// ---------------------------------------------------------------- SCORE
function computeScores() {
var brands = getBrands();
var index = {};
brands.forEach(function (b) {
b.fam = {};
FAMILIES.forEach(function (f) { b.fam[f.name] = { num: 0, den: 0, scope: 0, gaps: 0, scaled: 0 }; });
b.num = 0; b.den = 0; b.rawNum = 0; b.rawDen = 0; b.scopeW = 0; b.total = 0;
b.pillar = {};
PILLARS.forEach(function (p) { b.pillar[p.id] = { n: 0, d: 0 }; });
index[b.key] = b;
});
var es = ss().getSheetByName(TABS.entry);
if (es && es.getLastRow() > 1) {
es.getRange(2, 1, es.getLastRow() - 1, 5).getValues().forEach(function (r) {
var b = index[r[0]]; if (!b) return;
var fam = r[1]; var lvl = levelFor(r[4]); var w = weightFor(b.relevance[fam] || 'Core Priority');
b.total++;
b.rawDen += MAX_LEVEL; b.rawNum += (lvl === null ? 0 : lvl);
if (w === 0 || lvl === null) return;
b.scopeW += w;
b.num += lvl * w; b.den += MAX_LEVEL * w;
var f = b.fam[fam]; if (!f) return;
f.num += lvl * w; f.den += MAX_LEVEL * w; f.scope++;
if (lvl >= 4) f.scaled++;
if (lvl <= 1) f.gaps++;
});
}
var cs = ss().getSheetByName(TABS.checks);
if (cs && cs.getLastRow() > 1) {
cs.getRange(2, 1, cs.getLastRow() - 1, 4).getValues().forEach(function (r) {
var b = index[r[0]]; if (!b) return;
var pid = null;
PILLARS.forEach(function (p) { if (p.name === r[1]) pid = p.id; });
if (!pid) return;
var v = answerValue(r[3]);
if (v === null) return;
b.pillar[pid].n += v; b.pillar[pid].d += 1;
});
}
brands.forEach(function (b) {
b.adoption = b.den ? (b.num / b.den) * 100 : 0;
b.raw = b.rawDen ? (b.rawNum / b.rawDen) * 100 : 0;
b.coverage = b.total ? (b.scopeW / b.total) * 100 : 0;
b.composite = b.adoption * ADOPTION_WEIGHT;
PILLARS.forEach(function (p) {
var pv = b.pillar[p.id].d ? (b.pillar[p.id].n / b.pillar[p.id].d) * 100 : 0;
b.pillar[p.id].score = pv;
b.composite += pv * p.weight;
});
b.ambition = (b.adoption / (ambitionFor(b.tier) * 100)) * 100;
b.tierLabel = tierLabel(b.composite);
});
return brands;
}
function tierLabel(score) {
for (var i = 0; i < TIERS.length; i++) if (score >= TIERS[i].min) return TIERS[i].label;
return TIERS[TIERS.length - 1].label;
}
function bandFor(score) {
if (score === null || score === '') return 'na';
if (score >= 75) return 'advanced';
if (score >= 50) return 'building';
if (score >= 30) return 'planned';
if (score >= 15) return 'early';
return 'blocked';
}
function paint(range, band) {
range.setBackground(BANDS[band][0]).setFontColor(BANDS[band][1]);
}
function colorByBand(s, col, rows) {
if (!rows) return;
var rules = [];
var map = [['Scaled & Optimized', 'advanced'], ['Adopted', 'building'], ['Piloting', 'planned'],
['Planned', 'early'], ['Not Started', 'blocked'], ['Not Applicable', 'na']];
map.forEach(function (m) {
rules.push(SpreadsheetApp.newConditionalFormatRule()
.whenTextEqualTo(m[0])
.setBackground(BANDS[m[1]][0]).setFontColor(BANDS[m[1]][1])
.setRanges([s.getRange(2, col, rows, 1)]).build());
});
s.setConditionalFormatRules(rules);
}
// ---------------------------------------------------------------- DASHBOARDS
function refreshAll() {
var brands = computeScores();
writeScores(brands);
writeExec(brands);
writeMatrix(brands);
writeChannel(brands);
}
function writeScores(brands) {
var s = sheet(TABS.scores, true);
var head = ['Brand — Audience', 'Budget Tier', 'Composite', 'Maturity Tier', 'Adoption (relevance-weighted)',
'Adoption (unweighted)', 'Scope Coverage', 'Ambition Index']
.concat(PILLARS.map(function (p) { return p.name; }))
.concat(FAMILIES.map(function (f) { return f.short; }));
header(s, head);
var rows = brands.map(function (b) {
var r = [b.key, b.tier, round(b.composite), b.tierLabel, round(b.adoption), round(b.raw),
round(b.coverage), round(b.ambition)];
PILLARS.forEach(function (p) { r.push(round(b.pillar[p.id].score)); });
FAMILIES.forEach(function (f) {
var d = b.fam[f.name];
r.push(d && d.den ? round((d.num / d.den) * 100) : '');
});
return r;
});
if (rows.length) {
s.getRange(2, 1, rows.length, head.length).setValues(rows);
for (var i = 0; i < rows.length; i++) {
for (var c = 8; c < head.length; c++) {
var v = rows[i][c];
paint(s.getRange(i + 2, c + 1), bandFor(v === '' ? null : v));
}
paint(s.getRange(i + 2, 3), bandFor(rows[i][2]));
}
}
s.getRange(1, 1, 1, head.length).setWrap(true);
s.autoResizeColumns(1, 2);
}
function writeExec(brands) {
var s = sheet(TABS.exec, true);
s.getRange(1, 1).setValue('EXECUTIVE HEAT MAP').setFontSize(14).setFontWeight('bold');
s.getRange(2, 1).setValue('Relevance-weighted adoption maturity. Out-of-scope families are excluded from each brand denominator.')
.setFontStyle('italic').setFontColor('#666666');
s.getRange(3, 1).setValue('Legend').setFontWeight('bold');
var legend = ['Leading 75+', 'Advancing 50-74', 'Building 30-49', 'Early 15-29', 'Nascent <15', 'Out of Scope'];
var keys = ['advanced', 'building', 'planned', 'early', 'blocked', 'na'];
legend.forEach(function (l, i) {
var c = s.getRange(4, i + 1).setValue(l).setFontWeight('bold').setHorizontalAlignment('center');
paint(c, keys[i]);
});
var head = ['Brand', 'Website', 'Audience', 'Budget Tier', 'Composite', 'Tier', 'Scope Coverage']
.concat(FAMILIES.map(function (f) { return f.short; }))
.concat(['Executive Readout']);
header(s, head, 6);
var rows = brands.map(function (b) {
var strengths = [], gaps = [];
FAMILIES.forEach(function (f) {
var d = b.fam[f.name];
if (!d || !d.den) return;
var v = (d.num / d.den) * 100;
strengths.push([f.short, v]); gaps.push([f.short, v]);
});
strengths.sort(function (a, c) { return c[1] - a[1]; });
gaps.sort(function (a, c) { return a[1] - c[1]; });
var readout = strengths.length
? 'Strength: ' + strengths[0][0] + ' (' + round(strengths[0][1]) + '). Focus: ' + gaps[0][0] + ' (' + round(gaps[0][1]) + ').'
: 'No in-scope innovation families.';
var r = [b.brand, b.website, b.audience, b.tier, round(b.composite), b.tierLabel, round(b.coverage) + '%'];
FAMILIES.forEach(function (f) {
var d = b.fam[f.name];
r.push(d && d.den ? round((d.num / d.den) * 100) : 'Out of Scope');
});
r.push(readout);
return r;
});
if (rows.length) {
s.getRange(7, 1, rows.length, head.length).setValues(rows);
for (var i = 0; i < rows.length; i++) {
for (var f = 0; f < FAMILIES.length; f++) {
var v = rows[i][7 + f];
var cell = s.getRange(7 + i, 8 + f).setHorizontalAlignment('center');
paint(cell, typeof v === 'number' ? bandFor(v) : 'na');
}
paint(s.getRange(7 + i, 5), bandFor(rows[i][4]));
}
}
s.getRange(6, 1, 1, head.length).setWrap(true);
s.setColumnWidth(head.length, 380);
}
function writeMatrix(brands) {
var s = sheet(TABS.matrix, true);
s.getRange(1, 1).setValue('FAMILY ADOPTION MATRIX — % of maximum maturity').setFontSize(13).setFontWeight('bold');
var head = ['Brand — Audience'].concat(FAMILIES.map(function (f) { return f.short; }))
.concat(PILLARS.map(function (p) { return p.name; })).concat(['Composite']);
header(s, head, 3);
var rows = brands.map(function (b) {
var r = [b.key];
FAMILIES.forEach(function (f) {
var d = b.fam[f.name];
r.push(d && d.den ? round((d.num / d.den) * 100) / 100 : '');
});
PILLARS.forEach(function (p) { r.push(round(b.pillar[p.id].score) / 100); });
r.push(round(b.composite) / 100);
return r;
});
if (rows.length) {
var rng = s.getRange(4, 1, rows.length, head.length);
rng.setValues(rows);
s.getRange(4, 2, rows.length, head.length - 1).setNumberFormat('0%').setHorizontalAlignment('center');
for (var i = 0; i < rows.length; i++) {
for (var c = 1; c < head.length; c++) {
var v = rows[i][c];
paint(s.getRange(4 + i, c + 1), v === '' ? 'na' : bandFor(v * 100));
}
}
// Portfolio average row
var avg = ['PORTFOLIO AVERAGE'];
for (var c2 = 1; c2 < head.length; c2++) {
var sum = 0, n = 0;
rows.forEach(function (r) { if (r[c2] !== '') { sum += r[c2]; n++; } });
avg.push(n ? sum / n : '');
}
var arow = 4 + rows.length;
s.getRange(arow, 1, 1, head.length).setValues([avg]).setFontWeight('bold')
.setBackground('#8e8e8e').setFontColor('#ffffff');
s.getRange(arow, 2, 1, head.length - 1).setNumberFormat('0%').setHorizontalAlignment('center');
}
s.getRange(3, 1, 1, head.length).setWrap(true);
s.setColumnWidth(1, 220);
}
function writeChannel(brands) {
var s = sheet(TABS.channel, true);
s.getRange(1, 1).setValue('CHANNEL DETAIL — innovation level readout').setFontSize(13).setFontWeight('bold');
header(s, ['Innovation Family', 'Group / Partner', 'Innovation', 'Brands In Scope', 'Scaled',
'Not Started / Planned', 'Portfolio Adoption %', 'Priority Flag'], 3);
var es = ss().getSheetByName(TABS.entry);
var agg = {};
var relByBrand = {};
brands.forEach(function (b) { relByBrand[b.key] = b.relevance; });
if (es && es.getLastRow() > 1) {
es.getRange(2, 1, es.getLastRow() - 1, 5).getValues().forEach(function (r) {
var rel = relByBrand[r[0]]; if (!rel) return;
var w = weightFor(rel[r[1]] || 'Core Priority');
var lvl = levelFor(r[4]);
if (w === 0 || lvl === null) return;
var k = r[1] + '|' + r[2] + '|' + r[3];
if (!agg[k]) agg[k] = { family: r[1], group: r[2], name: r[3], n: 0, num: 0, den: 0, scaled: 0, lag: 0 };
var a = agg[k];
a.n++; a.num += lvl * w; a.den += MAX_LEVEL * w;
if (lvl >= 4) a.scaled++;
if (lvl <= 1) a.lag++;
});
}
var rows = Object.keys(agg).map(function (k) {
var a = agg[k];
var pctv = a.den ? (a.num / a.den) * 100 : 0;
var flag = pctv < 30 && a.n >= 2 ? 'Portfolio Gap' : (pctv >= 75 ? 'Scale Story' : 'Monitor');
return [a.family, a.group, a.name, a.n, a.scaled, a.lag, round(pctv), flag];
}).sort(function (x, y) { return x[6] - y[6]; });
if (rows.length) {
s.getRange(4, 1, rows.length, 8).setValues(rows);
for (var i = 0; i < rows.length; i++) paint(s.getRange(4 + i, 7), bandFor(rows[i][6]));
}
s.autoResizeColumns(1, 8);
}
function addBrandRow() {
buildEntry();
buildChecklists();
refreshAll();
}
function round(n) { return Math.round(n * 10) / 10; }