Google Sheets

Tafsee Popup writes to your sheet through a small script that you add to the sheet yourself. The script is a "web app" with its own address; Tafsee sends each lead to that address and the script adds the row. Setting it up takes about three minutes.

What you need

• A Google account and the Google Sheet the leads should go to. You must be able to edit the sheet.
• In Tafsee Popup: a role that can manage integrations.
• Be signed in to one Google account only in the browser you use for the setup. With several accounts signed in, Google often opens Apps Script under the wrong one.

1. Add the script to your sheet

1. Open your Google Sheet, then Extensions > Apps Script.
2. Delete what is there, paste this script and save. The script is shown in the app with a copy button, and here:

function doPost(e) {
  var lock = LockService.getScriptLock();
  lock.waitLock(20000);
  try {
    var row = JSON.parse(e.postData.contents).row || {};
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    var last = sheet.getLastColumn();
    var headers = last > 0 ? sheet.getRange(1, 1, 1, last).getValues()[0].map(String) : [];
    Object.keys(row).forEach(function (key) {
      if (headers.indexOf(key) === -1) headers.push(key);
    });
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    sheet.appendRow(headers.map(function (key) {
      return row[key] === undefined ? '' : row[key];
    }));
    return answer({ ok: true });
  } catch (error) {
    return answer({ ok: false, error: String(error) });
  } finally {
    lock.releaseLock();
  }
}

function answer(body) {
  return ContentService.createTextOutput(JSON.stringify(body))
    .setMimeType(ContentService.MimeType.JSON);
}

3. Click Deploy > New deployment and choose the type Web app.
4. Set "Execute as" to Me and "Who has access" to Anyone, then Deploy and allow access when Google asks.
5. Copy the Web app URL (it ends with /exec) and paste it below.

The link of the sheet itself will not work. After connecting, use Send test lead to see a row appear.

2. Connect it in Tafsee Popup

1. Open Integrations and find Google Sheets. Click Connect.
2. Paste the web app URL into "Apps Script web app URL". It starts with https://script.google.com/macros/s/ and ends with /exec.
3. Click Connect.
4. Tick the campaigns that should send their leads to the sheet and click Save campaigns.
5. On the Google Sheets card, click Send test lead.

What you should see

A new row appears in the first tab of your sheet within a few seconds. The first time, the script also writes the header row. The app says: Test lead delivered. Check that it arrived.

The columns

The script makes one column per value and adds a new column by itself when a new value appears:

• capturedAt, email, firstName, lastName, phone, company
• consent (yes or no), couponCode
• campaign, site, pageUrl, device, country
• utmSource, utmCampaign
• One column for each of your own form questions, named after the field's ID.

You can reorder the columns in the sheet. Do not rename the header cells: the script finds a column by its header, and a renamed header makes it add a new column.

A value that starts with =, +, - or @ (a phone number such as +966…, for example) is stored as text, so the sheet does not read it as a formula.

Problem 1: "Access blocked: Authorization Error"

When you deploy the script, or when Google asks you to allow access, Google shows a page like this instead:

Access blocked: Authorization Error
The OAuth client was not found.
Error 401: invalid_client

Why it happens

This message comes from Google, before Tafsee is involved at all. Every Apps Script project is tied to a Google Cloud project behind the scenes. Here that Cloud project is missing or broken, so Google cannot show the permission screen. It usually happens when the script was created under a different Google account than the one that owns the sheet, when it was copied from somewhere else, or when its Cloud project was changed or deleted.

The cure

1. Sign out of all Google accounts in the browser, then sign in to one only: the account that owns the sheet. A private window with just that account works well.
2. Open the sheet itself and choose Extensions > Apps Script from the sheet's own menu. Do not start from script.google.com and do not reuse an old script project.
3. A fresh project opens. Delete what is there, paste the script from above and save.
4. Click Deploy > New deployment, choose the type Web app, set "Execute as" to Me and "Who has access" to Anyone, and click Deploy.
5. Google now asks you to authorise the script. Choose your account. If Google says the app is not verified, open Advanced and continue to the project; it is your own script.
6. Copy the new web app URL and use it in Tafsee Popup.

If you must keep the existing script project: open it, click Project Settings (the gear), and look at "Google Cloud Platform (GCP) Project". Change it back to the default project, or to a Cloud project you own that has an OAuth consent screen set up. For most people the fresh script is quicker.

Problem 2: leads are marked "Failed" for Google Sheets

The sheet is connected, and yet leads carry Failed in the leads list, or the test lead does not arrive. Open a lead: under Where this lead went, after The tool answered:, you read:

Google asked for a sign-in, so the web app is not open to Tafsee. In Apps Script open Deploy > Manage deployments, set "Who has access" to Anyone, and deploy again.

Why it happens

The web app was deployed with "Who has access" set to "Only myself" or to people in your organisation. Tafsee is not signed in to your Google account, so Google answers with its sign-in page instead of running the script. Sending the same lead again would get the same answer, so Tafsee marks it failed at once and does not keep retrying.

The cure

1. Open the sheet, then Extensions > Apps Script.
2. Click Deploy > Manage deployments.
3. Select your web app deployment and click the pencil to edit it.
4. Set "Execute as" to Me.
5. Set "Who has access" to Anyone.
6. Under Version, choose New version.
7. Click Deploy.
8. Compare the web app URL with the one you pasted in Tafsee Popup. Editing a deployment this way keeps the same URL. If you made a new deployment, the URL is different: connect Google Sheets again with the new URL.
9. In Tafsee Popup, open Audiences, click Failed only, tick the leads and click Send again. See Lead delivery and Send again.

"Anyone" means anyone who has the web app's address can add a row through the script. It does not make the sheet itself public: nobody can read or open your sheet with that address. Keep the address to yourself.

A Google Workspace account can be set by its administrator not to offer "Anyone". If the choice is missing, ask your administrator, or create the sheet and the script under a personal Google account.

Other messages, and what to do

• "That is the link of the sheet itself." You pasted the address of the sheet (docs.google.com). Paste the web app URL from Deploy; it ends with /exec.
• "That is the test link (/dev), which only works for you." Use the URL from Deploy > Manage deployments; it ends with /exec.
• "Google could not find that web app." The deployment was archived or the URL is incomplete. Copy the URL again from Deploy > Manage deployments.
• "The Apps Script has no doPost function." The script was not pasted, or another one is there. Paste the script from this page, then deploy a new version.
• "The Apps Script could not add the row: …" The script ran and reported an error, which follows the colon. The most common one is that the account the script runs as can no longer edit the sheet.
• "The Apps Script stopped with an error." Open the script, look at Executions on the left for the error, fix it and deploy a new version.
• You changed the script and nothing changed: a web app keeps running the version that was deployed. After every change, deploy a new version (Deploy > Manage deployments > edit > New version > Deploy).
• Rows go to the wrong tab: the script writes to the first tab of the spreadsheet. Drag the tab you want to the first place.
• No campaign sends leads here yet, so nothing will arrive. Click Choose campaigns.

Related

• Lead delivery and Send again
• Leads are not arriving in a tool
• Connecting a tool: how it works