New Voco now answers WhatsApp and SMS, sorts your email and follows up for you.
New Voco now answers WhatsApp and SMS, sorts your email and follows up for you. See what's new

Developers

How to get Voco to ring a customer from Google Sheets

Paste one Google Apps Script into your sheet, and Voco rings the customer on any row in your opening hours, then writes the answer and the result back into the sheet.

Updated ยท Takes about 20 minutes

To get Voco to ring a customer from Google Sheets, switch on call requests under Settings, then Callbacks in your Voco dashboard, copy your private address, and paste one Google Apps Script into your sheet. Then click a customer's row and choose Voco, then Ring this person. Voco rings that number as your AI receptionist, in your business name, within your opening hours and never before 8am or after 9pm, and the sheet shows whether the call was booked and when it will ring. Setup takes about 20 minutes and needs no Zapier or other app.

What does the Google Sheets set-up do?

The Google Sheets set-up turns any row in your sheet into a phone call from your Voco receptionist. Each row holds a customer's name, their phone number, what the call is about, and optionally a message to pass on and a question to ask. A menu item sends that row to Voco's webhook, the same private address the Voco webhooks integration describes, and writes Voco's answer back into the row.

Voco checks it is talking to the right person before it says why it is ringing, introduces itself by your receptionist's name as your AI assistant, and checks it is a good time. Each call appears in your Voco dashboard with its recording, transcript and summary, the same as calls that come in.

Step 1: switch on call requests and copy your private address

Call requests from other apps are switched off until you turn them on. Only an owner or admin on your Voco account can do this.

  1. In your Voco dashboard, open Settings, then Callbacks.
  2. Find the card Accept call requests from a private address and switch it on.
  3. Press Save changes.
  4. Copy Your private address. It ends with /call.

Treat your private address like a password: anyone who has it can ask your receptionist to ring someone. If it ever gets out, press Make a new one on the same card and paste the new address into your script.

Step 2: set up your sheet

Give the first tab of your sheet these headings in row 1, one per column from A to I: Name, Phone, Reason, Message, Goal, Status, Rings at, Reference and Result. Put one customer on each row below. The script fills in the last four columns.

ColumnWhat to writeExample
NameWho the call is for. Optional, but Voco then checks it is them before it says why it is ringing.Jamie Example
PhoneA UK number, written any usual way. Required.07700 900123
ReasonA short topic that follows "I'm calling about", up to 120 characters. Required.your boiler service
MessageSomething to pass on, up to 300 characters. Voco says it in its own words.The part has arrived.
GoalA question you want answered, up to 300 characters. Voco asks it as the call's first question.Could the engineer come on Thursday morning?

Keep the Reason short. Voco says it straight after "I'm calling about", so "your boiler service" reads well and a whole sentence does not. Put anything longer in Message.

Format the Phone column as plain text, so Google Sheets keeps the 0 at the start: select column B, then choose Format, then Number, then Plain text.

Step 3: add the script

The script adds a Voco menu to your sheet. It sends the selected row to Voco with Google's UrlFetchApp, set to muteHttpExceptions so every answer from Voco reaches the sheet, not only the good ones.

  1. In your sheet, choose Extensions, then Apps Script.
  2. Delete anything in the editor, then paste the script below.
  3. Replace PASTE YOUR PRIVATE ADDRESS HERE with the address you copied in step 1, keeping the quote marks.
  4. Press Save (the disk icon), then go back to your sheet and reload the page. A Voco menu appears beside Help.
// Voco: ring a customer from this sheet.
// Row 1 holds the headings. Columns: A Name, B Phone, C Reason, D Message,
// E Goal, F Status, G Rings at, H Reference, I Result.

const VOCO_ADDRESS = 'PASTE YOUR PRIVATE ADDRESS HERE';
const RESULT_KEY = 'MAKE UP A LONG RANDOM WORD';
const COL = { name: 1, phone: 2, reason: 3, message: 4, goal: 5, status: 6, ringsAt: 7, reference: 8, result: 9 };

function onOpen() {
  rememberThisSheet(SpreadsheetApp.getActiveSheet());
  SpreadsheetApp.getUi()
    .createMenu('Voco')
    .addItem('Ring this person', 'ringThisPerson')
    .addToUi();
}

function ringThisPerson() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const row = sheet.getActiveCell().getRow();
  rememberThisSheet(sheet);

  if (row < 2) {
    SpreadsheetApp.getUi().alert('Click a cell in the row of the person to ring first.');
    return;
  }

  const cells = sheet.getRange(row, 1, 1, 5).getDisplayValues()[0];
  const body = { phone: cells[COL.phone - 1], reason: cells[COL.reason - 1] };

  if (cells[COL.name - 1]) body.name = cells[COL.name - 1];
  if (cells[COL.message - 1]) body.message = cells[COL.message - 1];
  if (cells[COL.goal - 1]) body.goal = cells[COL.goal - 1];

  const response = UrlFetchApp.fetch(VOCO_ADDRESS, {
    method: 'post',
    contentType: 'application/json',
    headers: { Accept: 'application/json' },
    payload: JSON.stringify(body),
    muteHttpExceptions: true,
  });

  const code = response.getResponseCode();
  let reply = {};

  try {
    reply = JSON.parse(response.getContentText());
  } catch (error) {
    reply = {};
  }

  sheet.getRange(row, COL.status).setValue(statusFor(code, reply));

  // Only a reply that names a call replaces the row's reference and time, so a
  // refused repeat leaves the waiting call's result able to find its row.
  if (reply.callback) {
    sheet.getRange(row, COL.reference).setValue(reply.callback);
    sheet.getRange(row, COL.ringsAt).setValue(whenFor(reply.rings_at || reply.next_attempt_at));
  }
}

// A web app can't ask which spreadsheet is open, so the sheet notes where it lives.
function rememberThisSheet(sheet) {
  PropertiesService.getScriptProperties().setProperty('SPREADSHEET_ID', sheet.getParent().getId());
}

function statusFor(code, reply) {
  if (code === 202) return 'Booked';
  if (reply.reason === 'already_scheduled') return 'A call is already waiting';
  if (reply.reason === 'opted_out') return 'Asked not to be called';
  if (reply.reason === 'not_a_uk_number') return 'Not a UK number Voco can ring';
  if (reply.reason === 'low_balance') return 'Not rung: top up or wait for your allowance';
  if (reply.reason === 'daily_cap') return 'Daily limit reached, try tomorrow';
  if (reply.reason === 'busy_try_again') return 'Busy, try again in a moment';
  if (reply.reason === 'unknown_or_switched_off') return 'Check the address, and that call requests are on';
  if (reply.reason === 'not_in_beta' || reply.reason === 'business_inactive') return 'Calls from other apps are not on for this account';
  if (reply.errors) return 'Check this row: ' + Object.keys(reply.errors).join(', ');
  return 'Not booked (' + code + '): ' + (reply.reason || reply.message || 'try again');
}

function whenFor(isoTime) {
  if (!isoTime) return '';
  return Utilities.formatDate(new Date(isoTime), 'Europe/London', 'EEE d MMM, h:mm a');
}

// Optional: Voco sends each call's result here once the call is over.
function doPost(e) {
  if (!e || !e.parameter || e.parameter.key !== RESULT_KEY) {
    return ContentService.createTextOutput('Not for this sheet');
  }

  const result = JSON.parse(e.postData.contents);
  const where = PropertiesService.getScriptProperties().getProperties();

  if (!where.SPREADSHEET_ID) {
    return ContentService.createTextOutput('Open the sheet once first');
  }

  // Every tab is searched for the call's reference, so it doesn't matter which tab was open last.
  const summary = result.note || (result.call && result.call.summary) || result.skip_reason || '';
  const sheets = SpreadsheetApp.openById(where.SPREADSHEET_ID).getSheets();
  let updated = 0;

  for (let s = 0; s < sheets.length; s++) {
    const sheet = sheets[s];
    const lastRow = sheet.getLastRow();

    if (lastRow < 2 || sheet.getLastColumn() < COL.reference) continue;

    const references = sheet.getRange(2, COL.reference, lastRow - 1, 1).getDisplayValues();

    for (let i = 0; i < references.length; i++) {
      if (references[i][0] === String(result.callback_id)) {
        sheet.getRange(i + 2, COL.status).setValue(result.outcome || result.status);
        sheet.getRange(i + 2, COL.result).setValue(summary);
        updated++;
      }
    }
  }

  // Every row with this reference is updated: two rows for one number share the call.
  return ContentService.createTextOutput(updated > 0 ? 'OK' : 'No row for that reference');
}

The first time you use the menu, Google asks you to allow the script to read your sheet and connect to an external service. Choose your Google account and press Allow. If Google says it has not verified the app, that is because the script is your own: choose Advanced, then the link to go to your project.

Step 4: ring someone from the sheet

Click any cell in a customer's row, then choose Voco, then Ring this person. Within a few seconds the row shows Voco's answer in Status, when the call will ring in Rings at, and the call's reference in Reference.

A request that arrives while you are closed is accepted and waits, so pressing the menu at 10pm books the call for your next opening time. Each number gets one call at a time. Wait for a call to finish, or call it off, before you ask Voco to ring the same number again.

What do the statuses in the sheet mean?

The Status column is the script's plain-words version of Voco's answer. The webhooks integration page lists every answer and field in full.

StatusWhat it meansWhat to do
BookedVoco will ring at the time in Rings at.Nothing.
A call is already waitingThat number already has a call waiting, ringing or waiting to try again.Wait for it, or call it off in Voco and ask again.
Asked not to be calledSomebody on that number told Voco not to call again.The account owner can press Allow calls again on that customer's page, if they have since said it is fine.
Not a UK number Voco can ringVoco rings UK numbers, and not premium rate or personal numbers.Check the number in the Phone column.
Check this rowA field is missing or too long. The status names it.Fix that cell and try again.
Not rung: top up or wait for your allowanceNot rung, to keep your remaining calls free for customers ringing in.Top up your balance, or wait for your plan's next month.
Daily limit reached, try tomorrowYour business has asked for the most calls allowed in one day.Try again after midnight, UK time.
Check the address, and that call requests are onVoco does not recognise the address, or call requests are switched off.Copy the address again from Settings, then Callbacks.

Step 5 (optional): get each call's result back into the sheet

Voco can send how each call went to an address you choose, once the call is over: whether they answered, a short note and the call's summary. The script's doPost part receives it and writes it into the row with the same Reference.

  1. In the script, replace MAKE UP A LONG RANDOM WORD with a long word only you know, such as a string of 20 letters and numbers. Press Save.
  2. Choose Deploy, then New deployment. Pick the type Web app, set Execute as to Me and Who has access to Anyone, then press Deploy.
  3. Copy the web app URL, which ends in /exec, and add ?key= followed by your long word.
  4. In Voco, open Settings, then Callbacks, paste that address into Send the result back to, and press Save changes.

The key in the address is how the script knows a result came from Voco. Voco also signs every result with your private address in an X-Voco-Signature header, but a Google Apps Script web app cannot read request headers: its doPost event carries the body and the address's parameters only. Apps that can read headers, such as your own code, should check the signature instead.

Google answers each result with a redirect. Voco counts that as delivered, so each result is sent once. If your web app ever fails, Voco tries again up to 3 times, and the script writes to the same row each time, so nothing is doubled.

How do I test it safely?

Test with a second phone that has never rung your Voco number. If the number Voco is about to ring calls your Voco line while the call waits, Voco may cancel it, because their own call usually sorts it out. Saying "stop calling me" or "I never asked for this" on a test call marks that number as opted out. The account owner can undo it with Allow calls again on that customer's page. See how to make your first test call for checking how your receptionist sounds.

How do I see or call off a call Voco is going to make?

Open Calls, then Voco's calls in your Voco dashboard. Calls Voco will make lists each waiting call, who it is for, what it is about and when it rings, with Call off and Ring now buttons for an owner or admin. Recent callbacks shows how the last 10 ended, in plain words, such as "Spoke to them", "No answer" or "Called off".

What if it does not work?

  • No Voco menu: reload the sheet after saving the script. The menu is added when the sheet opens.
  • The Phone column lost its first 0: format column B as plain text and type the number again.
  • Every row says "Check the address": call requests may be switched off, or someone pressed Make a new one. Copy the address again and paste it into the script.
  • Nothing in the Result column: open the sheet once after saving the script, so it notes which spreadsheet to write to (ringing someone from the Voco menu does the same). Then check the web app's access is Anyone, the result address ends in /exec?key= and your word, and the word matches the script. Results arrive a few minutes after the call ends.
  • A call you expected has not rung: look under Calls, then Voco's calls. It may be waiting for your opening hours, or for its next try after nobody answered.

Common questions

Still stuck?

Email hello@vocohq.co.uk with the step you are on and what you see, and the Voco team replies within 2 hours during business hours.

We value your privacy. We use cookies to understand how the site is used, and only after you accept. How we use cookies