Connecting Google Sheets
You keep a register in Google Sheets — customer complaints, say — and you want each new row to kick off the whole process in killBottleneck? This page walks you through it. You do not need to know how to code: the code is written, you fill in a few lines.
The example here is a complaint handled with the 8D method, but the same thing works for anything else — orders, hiring, service calls. Only the names change.
What exactly happens
- A row for a new complaint is added to the sheet.
- A goal called "Complaint 0001 — Novak Automotive" appears in the map.
- killBottleneck expands the eight disciplines D1–D8 by itself and tells whoever owns it.
- As people tick the disciplines off, the sheet gets "D4 – Root cause (3/8)" written back into it.
Do you run n8n? Go straight there instead
This page drives the spreadsheet through Google Apps Script, which is part of Sheets and needs nothing else. If you have n8n, that is the easier and more capable route — steps 1–3 below are shared, then continue with Connecting through n8n.
Before you start
You need three things and about half an hour:
- a killBottleneck account — this works identically in the cloud and on your own server,
- a Google Sheet whose first row holds the column names,
- ten minutes of clicking and ten of pasting ready-made code.
Own server inside a company network? Read this first
Google Apps Script does not run in your office — it runs on Google's servers. For Google to write into killBottleneck, it has to reach it from the internet.
- A cloud instance (
yourcompany.killbottleneck.com) — reachable, nothing to solve. - Your own server on a public address — reachable, just give it HTTPS.
- Your own server on the company network only — not reachable. Either expose it on a public address, or take the n8n route and run n8n in the same network. The other direction — killBottleneck writing out to the sheet — works from anywhere.
1. Prepare the map: a tree or a board?
First decide what the work should look like in the map. Both do the same job, they differ in the view — and that choice changes exactly two lines of configuration later, nothing else.
Option A — a tree (the default)
Every complaint carries its own eight disciplines.
Create a new project (say Complaints 2026) and put one goal under the main goal, called something like New complaints. That is what new rows will hang under.
Option B — a kanban board
Eight columns side by side, with the complaint as a card travelling across them.
Two clicks create it: New project → From template → 8D report — kanban.
| Which one to pick | |
|---|---|
| Tree | You want to see all eight steps of a complaint at once, who owns them and when they are due. |
| Board | You want to see at a glance how many cases are stuck on which discipline. |
Either way, the map id sits in the address bar with the map open: …/map/y2cmuebk7guaev1. Write it down, you will need it.
2. Make the procedure run itself
The board — nothing to do
The 8D report — kanban template brings the columns and the seven move rules. Each one says: a card under Dx switched to Done → move it under Dx+1 → set it back to To do. Done — carry on with step 3.
With the tree you add one rule:
- In the map click the lightning bolt → Rules (on a narrower screen it is under ⋮).
- New rule, and set:
- When: Node created
- If: parent goal is New complaints
- Do: Create subgoals and list the eight disciplines D1 to D8
Do not skip that "if" condition
Without it the eight steps expand under every new goal in the map — including the eight the rule has just created. The condition on the parent goal is what prevents that.
Want an owner and a notification too? Add the Set owner and Notify actions. There is more in Automation rules.
3. Create an API key
The key is the password your spreadsheet introduces itself with.
- Top right, user menu → API keys.
- Label it
Google Sheets, set Permission: Read and write. - Create, and copy the key straight away — it is shown only once. If you lose it, it cannot be recovered, only revoked and reissued.
The key acts as you and reaches only your maps. It can never do admin work, even if it leaks — details in the REST API.
That is the end of the shared part. If you are going the n8n way, head to Connecting through n8n. The rest of this page is the Apps Script route.
4. Columns in the sheet
Set your sheet up with these columns (the order does not matter, the names do):
| Complaint number | Customer | Problem description | killBottleneck | 8D status |
|---|---|---|---|---|
| 0001 | Novak Automotive | Cracked weld on the bracket. | (leave empty) | (leave empty) |
The script fills the last two columns in itself — killBottleneck remembers which goal the row maps to, 8D status says how far the work has got.
5. Paste the script
In the sheet go to Extensions → Apps Script, delete whatever is there, and paste the whole code below.
The whole Apps Script code (expand and copy)
/**
* killBottleneck ↔ Google Sheets
* ------------------------------------------------------------------
* What it does:
* 1) turns every new row of your complaint register into a goal in the map,
* which killBottleneck then expands into a full 8D report on its own,
* 2) writes the progress back into the sheet ("D4 – Root cause (3/8)").
*
* You do not need to know how to code. Fill in the configuration below.
*/
// ⚙️ ------------------- FILL IN THE CONFIGURATION -------------------
/** Your instance address. In the cloud it is what you see in the browser, WITHOUT a trailing slash. */
const KB_ADDRESS = 'https://yourcompany.killbottleneck.com';
/** API key. Create it in the app: your name in the top right → API keys,
* and it MUST have the "Read and write" permission. It is shown only once, so copy it right away. */
const KB_KEY = 'kb_user_paste_your_key_here';
/** Map id. You can see it in the address bar with the map open: /map/y2cmuebk7guaev1 */
const KB_MAP = 'paste_your_map_id_here';
/** The goal new complaints hang under.
* Classic map: 'New complaints'. Kanban board: the first column, i.e. 'D1 – Team formation'. */
const KB_BRANCH = 'New complaints';
/** Do you have a kanban board (a card travels along the columns after "Done")? Set true. */
const KB_KANBAN = false;
// ⚙️ ----------------------------------------------------------
/**
* What the columns are called. The script looks them up by the NAME in the first row,
* so they can be in any order — they just have to be named like this.
*/
const COLUMNS = {
number: 'Complaint number',
customer: 'Customer',
description: 'Problem description',
link: 'killBottleneck', // the script stores the goal here — do not edit it
status: '8D status', // the script writes the progress here
};
/** The menu in the sheet (it appears after you reopen the file). */
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('killBottleneck')
.addItem('Send new complaints', 'sendNewComplaints')
.addItem('Fetch progress', 'fetchProgress')
.addToUi();
}
/**
* STEP 1: every row that does not have the "killBottleneck" column filled in yet
* becomes a new goal in the map. Rows that are already there are skipped — so
* nothing gets duplicated, not even if you run this ten times in a row.
*/
function sendNewComplaints() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getDataRange().getValues();
const col = kbFindColumns_(data[0]);
let created = 0;
for (let r = 1; r < data.length; r++) {
const row = data[r];
if (row[col.link]) continue; // already sent
if (!row[col.number] && !row[col.description]) continue; // empty row
const title = 'Complaint ' + row[col.number] + ' — ' + row[col.customer];
const description = String(row[col.description] || '');
const goalId = kbCreateGoal_(title, description);
sheet.getRange(r + 1, col.link + 1).setValue(goalId);
created++;
}
kbToast_(created ? 'New complaints sent: ' + created : 'Nothing new to send.');
}
/**
* STEP 2: reads the map and writes next to each row where the work stands.
* This is the feedback into your register — when somebody completes a discipline
* in the map, the sheet follows.
*/
function fetchProgress() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getDataRange().getValues();
const col = kbFindColumns_(data[0]);
const map = kbCall_('GET', '/api/kb/v1/maps/' + KB_MAP);
const byId = kbIndex_(map.tree);
let updated = 0;
for (let r = 1; r < data.length; r++) {
const goalId = data[r][col.link];
if (!goalId) continue;
const entry = byId[goalId];
const text = entry ? kbProgressText_(entry) : 'no longer in the map';
if (data[r][col.status] !== text) {
sheet.getRange(r + 1, col.status + 1).setValue(text);
updated++;
}
}
kbToast_('Rows updated: ' + updated);
}
/**
* One sentence for the sheet. KB_KANBAN decides how it is read:
*
* CLASSIC map — the complaint has the eight disciplines under it, count the done ones.
* KANBAN — the complaint is a card with no subgoals that travels to the next
* column after "Done"; report the column it currently stands in.
*/
function kbProgressText_(entry) {
const goal = entry.goal;
if (KB_KANBAN) {
const columns = entry.parentRow || [];
const position = columns.indexOf(entry.parent) + 1;
if (!position) return 'not under any column';
if (position === columns.length && goal.status === 'done') return '✅ closed (' + position + '/' + columns.length + ')';
return entry.parent.title + ' (' + position + '/' + columns.length + ')';
}
const steps = goal.children || [];
if (!steps.length) return goal.status === 'done' ? 'closed' : 'waiting for the 8D to expand';
const done = steps.filter(function (k) { return k.status === 'done'; }).length;
if (done === steps.length) return '✅ closed (' + done + '/' + steps.length + ')';
const firstOpen = steps.filter(function (k) { return k.status !== 'done'; })[0];
return firstOpen.title + ' (' + done + '/' + steps.length + ')';
}
/**
* Creates one goal under the KB_BRANCH goal and returns its id.
*
* Why this is not a single call: on every write killBottleneck wants to know which
* version of the map you saw (`base_updated`). If somebody changed it in the meantime,
* it answers 409 and asks you to read the map again. That is a safeguard against
* overwriting someone else's work — here we simply handle it and try again.
*/
function kbCreateGoal_(title, description) {
for (let attempt = 1; attempt <= 4; attempt++) {
const map = kbCall_('GET', '/api/kb/v1/maps/' + KB_MAP);
const branch = kbFindByTitle_(map.tree, KB_BRANCH);
if (!branch) throw new Error('The map has no "' + KB_BRANCH + '" goal.');
const response = kbCall_('POST', '/api/kb/v1/maps/' + KB_MAP + '/nodes', {
base_updated: map.updated,
parent_id: branch.id,
items: [{ title: title, description: description }],
}, true);
if (response.code === 200) return response.payload.added_ids[0];
if (response.code === 409) { Utilities.sleep(700 * attempt); continue; } // somebody changed the map
if (response.code === 429) { Utilities.sleep(3000 * attempt); continue; } // too fast
throw new Error('killBottleneck answered ' + response.code + ': ' + JSON.stringify(response.payload));
}
throw new Error('The map keeps changing, please try again in a moment.');
}
/** One call to killBottleneck. */
function kbCall_(method, path, payload, returnCode) {
const response = UrlFetchApp.fetch(KB_ADDRESS + path, {
method: method.toLowerCase(),
contentType: 'application/json',
headers: { Authorization: 'Bearer ' + KB_KEY },
payload: payload ? JSON.stringify(payload) : undefined,
muteHttpExceptions: true,
});
const code = response.getResponseCode();
let body = {};
try { body = JSON.parse(response.getContentText()); } catch (e) {}
if (returnCode) return { code: code, payload: body };
if (code === 401) throw new Error('Invalid API key — check KB_KEY.');
if (code === 404) throw new Error('Map not found — check KB_MAP.');
if (code !== 200) throw new Error('killBottleneck answered ' + code + ': ' + response.getContentText());
return body;
}
/** Finds the column numbers by the names in the first row. */
function kbFindColumns_(header) {
const result = {};
Object.keys(COLUMNS).forEach(function (key) {
const index = header.indexOf(COLUMNS[key]);
if (index === -1) throw new Error('The sheet has no "' + COLUMNS[key] + '" column.');
result[key] = index;
});
return result;
}
/**
* An index of goals. For each one it remembers the parent and THE ROW THE PARENT
* STANDS IN — for a kanban card that is exactly the row of columns, so its
* position can be counted.
*/
function kbIndex_(tree) {
const index = {};
const walk = function (goals, parent, parentRow) {
(goals || []).forEach(function (u) {
index[u.id] = { goal: u, parent: parent, parentRow: parentRow };
walk(u.children, u, goals);
});
};
walk(tree, null, []);
return index;
}
function kbFindByTitle_(goals, title) {
let found = null;
kbWalkTree_(goals, function (u) { if (!found && u.title === title) found = u; });
return found;
}
function kbWalkTree_(goals, fn) {
(goals || []).forEach(function (u) { fn(u); kbWalkTree_(u.children, fn); });
}
/** A message for the user — a toast in the sheet, or just the log on a scheduled run. */
function kbToast_(text) {
try { SpreadsheetApp.getActiveSpreadsheet().toast(text, 'killBottleneck', 5); }
catch (e) { Logger.log(text); }
}6. Fill in the configuration
There are five lines at the top of the script. The first three always apply:
const KB_ADDRESS = 'https://yourcompany.killbottleneck.com';
const KB_KEY = 'kb_user_paste_your_key_here';
const KB_MAP = 'paste_your_map_id_here';The last two follow what you picked in step 1:
| Shape of the map | KB_BRANCH | KB_KANBAN |
|---|---|---|
| Tree | 'New complaints' | false |
| Board | 'D1 – Team formation' | true |
Save (💾), pick the sendNewComplaints function at the top and hit Run. Google will ask for permission the first time — approve it. The goal ids appear in the sheet.
7. Progress back into the sheet
You already have the other direction too — the fetchProgress function in the same script. It reads the map and writes next to each row where the work stands: D2 – Problem description (1/8) on a tree, or the column the card stands in on a board.
8. Making it run by itself
So far you run both by hand. Turn on automatic runs in Apps Script under the alarm-clock icon on the left (Triggers) → Add trigger:
| Function | Source | Type | What for |
|---|---|---|---|
sendNewComplaints | From spreadsheet | On change | a new row travels into the map right away |
fetchProgress | Time-driven | Hour / minutes timer | progress comes back into the sheet |
Reopen the spreadsheet and you also get a killBottleneck menu at the top, from which both can be run by hand.
When it does not work
| What you see | What is going on |
|---|---|
Invalid API key (401) | The key is mis-copied, revoked or expired. Create a new one. |
Map not found (404) | Wrong map id — or the key belongs to someone who cannot reach that map. |
403 on a write | The key is read-only. Create a new one with Read and write. |
409 over and over | Somebody is editing the map heavily right now. The script retries on its own; if it persists, try again later. |
429 | Too many writes per minute (the cap is 30). Send rows in batches, not one by one. |
The map has no … goal | KB_BRANCH does not match a title in the map. On a board it must be the first column. |
| The goal appears but the 8D does not expand | The rule is missing, disabled, or its "parent goal" condition points elsewhere. Check Rules → run log. |
| A column is missing | The script looks columns up by the name in the first row. A typo in the header breaks it. |
| Nothing happened at all | The trigger did not fire — check Executions in the left sidebar of Apps Script. |
What to expect, and what not
So that nothing surprises you later:
- Deadlines are in days, not hours. If your procedure says "interim containment within 24 hours", the finest a rule can set is tomorrow.
- The order of goals in the map is not the order they were created in. The map rearranges itself, which is why the recipe remembers the goal id, not its position.
- Rules only ever apply going forward. Complaints already in the map are not expanded retroactively, nor do they start moving.
- Rule firings are neither counted nor billed, and the cap is 50 rules per map.
- killBottleneck does not produce the finished 8D form for your customer. It watches the progress and the deadlines; keep that document wherever you keep it today.
Where next
- Connecting through n8n — the same thing when you have n8n
- REST API — every endpoint, the limits and the error codes
- Automation rules — everything a map can do by itself