用 Google Sheets 取得電子報中點擊回覆結果

/

本篇要解決的問題

這篇算是湊數之前寫過的筆記文的集合應用,前幾天 August 在公司接到一個小需求,想想後覺得行銷人員應該會用得到,就整理成這篇。如標題上寫的,這次的需求是行銷人員發送的 EDM 是一個邀請函,裡面有「要參加」、「不參加」二個按鈕,想紀錄點擊「參加」或「不參加」的人各有誰。

一般來說,公司的資料最好是都進公司的資料庫,但因為這次要紀錄的只有 email 而已,沒有其他資訊,為了讓行銷人員更好看到資料,就決定直接把點擊結果紀錄在 Google Sheets 中。

本篇主要用到的是之前寫過的四篇:

完全客製 Google 表單,美化表單樣式

用 Vue.js 製作圖片版 EDM 生成器

如何用 Netlify CMS 製作電子報生成器

製作 RWD email 工具:MJML,如何使用及注意事項

本篇會一路實作下去,不會有太多詳細解釋,想知道細部的操作方式,麻煩點進上面的筆記文中觀看囉。


製作一份電子報

上面有提供二篇的連結,可以自行參考製作一份電子報生成器出來,或是用 MJML 寫一份,或是直接找網路上的模版下載後改個圖片跟文字。

這是廣告,點擊一下可以幫本站多個一點點的廣告收入,謝謝

這段要注意的重點是,我們的電子報上要有「Yes」、「No」相關的二個按鈕。

而且按鈕的連結上,必須帶入參數。

August 做了一個簡單的電子報,大家可以點下面的連結來看(對真的很簡單我知道):

https://letswritetw.github.io/google-sheets-edm-reply/edm.html

裡面有二個按鈕,分別是訂閱跟不訂閱,各自的連結為:

訂閱:xxxxx/?result=y&email=xxxxx@xxx.xxx

不訂閱:xxxxx/?result=n&email=xxxxx@xxx.xxx

result 代表的是訂閱(y)或不訂閱(n)。

email 就是收件者的 email。

這是廣告,點擊一下可以幫本站多個一點點的廣告收入,謝謝

必須在發送電子報時就把參數給帶進連結中,一般發報系統應該是可以做到這點,做不到的話……本篇其實也不用往下看了,因為這是最重要的第一步。


製作一個 Google 表單,抓需要的欄位值

要把資料寫進 Google Sheets,August 用的是提交結果到 Google 表單的方法。

在 Google 雲端硬碟中新增一個 Google 表單,我們要紀錄的是收件人的 email 跟點擊訂閱 / 不訂閱的結果,因此只需要這二題,August 的範例如下:

上面是嵌入的 Google 表單,麻煩不要填寫啊,我們後續會用程式碼自動填入收件人的回覆。

Google 表單題目創好後,「回覆」那邊要點擊「建立試算表」,把 Google 表單收到的每一個提交都紀錄在 Google Sheets 中。

Google 表單及接收回覆的試算表都建立完成,接著我們需要抓二個值:form action、欄位的 key。

form action 是我們之後 POST 需要的 URL。

欄位的 key,就是傳送資料的 key 值。

有了這二項,我們就可以用 POST 的方式去提交 Google 表單。

在 Google 表單的頁面點右鍵按「檢查」,開啟看到原始碼元素的面版,程式碼中搜尋「<form」,複製裡面的 action 值,這個就是 form action,如下圖:

找到 Google 表單中的 form action
找到 Google 表單中的 form action

在 August 的範例中,form action 就是:

https://docs.google.com/forms/u/0/d/e/1FAIpQLSd6oVaoHcBe1smAYMfIeAFDA5ifOBPY2-bdGRjzMWLXCV96xw/formResponse

接著我們在程式碼中搜尋「entry.」,會看到有二個 hidden 的 input,因為我們的題目是二題,input 就會有二個,照順序分別對應到的題目是:

是否訂閱:entry.1290516544

email:entry.265887320

找到各題的 key 值
找到各題的 key 值

這個就是我們需要的欄位 key,之後 POST 要傳資料時,就寫成以下:

var result = {
	'entry.1290516544': 是否訂閱的結果,
  	'entry.265887320': 收件者的 email
}

建立回覆結果頁

最後我們要建立一個頁面,是當收件者收到 email 後,點擊訂閱或不訂閱時,要進到的頁面。

這個頁面也就是我們要執行「把結果提交到 Google 表單」的頁面。

抓取網址參數

我們在建立電子報那段有說,按鈕上的連結必須帶入參數,本篇 Demo 頁的參數為:

訂閱:xxxxx/?result=y&email=xxxxx@xxx.xxx

不訂閱:xxxxx/?result=n&email=xxxxx@xxx.xxx

在回覆結果頁中,我們要把 resultemail 這二個參數的值給抓下來,抓網址參數的程式碼如下:

function getSearch(name) {
  const href = window.location.search.substring(1);
  const objectHrefs = href.split("&");
  for (let i = 0, len = objectHrefs.length; i < len; i++)	{
    let objectHref = objectHrefs[i].split("=");
    if(objectHref[0] == name) {
      return objectHref[1];
    }
  }
  return;
}
// 取得網址上 email、result 的值
const email = getSearch('email');
const result = getSearch('result') === 'y' ? '是' : '否';

這邊 August 多了一個動作,就是判斷 result 的值是「y」或「n」,紀錄上 Google Sheets 時轉成「是」或「否」,這步不一定要有,只是因為之後看試算表的人會是行銷人員,寫 y 或 n 會沒這麼直覺,因此多轉成中文給他們看。(覺得 August 貼心的人麻煩給本篇點個讚)

把結果 POST 到 Google 表單

抓到了網址上的參數,也就是收件者點擊回覆的結果後,就可以把結果用 POST 的方式提交到 Google 表單上。

const result = getSearch('result') === 'y' ? '是' : '否';
const email = getSearch('email');
// 提交到 Google 表單
if(email) {
  let formUri = 'Google 表單 form action 的網址';
  let key_result = 'entry.xxxx';
  let key_email = 'entry.xxxx';
  const myHeaders = new Headers();
  const formdata = new FormData();
  formdata.append(key_email, email);
  formdata.append(key_result, decodeURIComponent(result));
  const requestOptions = {
    method: 'POST',
    headers: myHeaders,
    body: formdata,
    redirect: 'follow'
  };
  fetch(formUri, requestOptions)
    .catch(error => console.log('error', error));
}

上面的程式碼中,有幾點要注意。

if(email) { ... } 這是確保網址上有 email 參數時才執行,不然有其他人不小時進到這頁時會因為抓不到參數而報錯。

decodeURIComponent(result) 因為傳送的結果會轉成「是 / 否」,是中文,必須先 decode 過後再傳,不然 Google Sheets 上看到的會是亂碼。

清掉網址上的 email 參數

網址上有 email,使用者看到的話會覺得有安全上的疑慮,因此在紀錄了結果後,最好是把網址上的 email 參數給清掉,這邊我們用 history.pushState

const r = getSearch('result');
const cleanUri = `${location.pathname}?result=${r}`;
history.pushState('', '', cleanUri);

網址上保留 result 這個參數,主要是為了要能知道收件人是點擊了什麼,給相對應的結果。

不同的結果,顯示不同的文字

上面一段中說,我們要保留 result 這個參數,因為要給相對應的結果。

本篇 Demo 中,有寫一段收件者點「訂閱」、「不訂閱」時,在結果頁上顯示不同的文字,這樣收件者點擊後才會知道自己點擊後有沒有成功,程式碼如下:

<p id="thanks"></p>
const result = getSearch('result') === 'y' ? '是' : '否';
const email = getSearch('email');
// 不同結果,給不同結果文字
let thanks;
result === '是' ? thanks = '謝謝您的訂閱,祝您有個美好的一天~' : thanks = '蝦密,你竟然不訂!好啦,還是祝您有個美好的一天~';
document.getElementById('thanks').textContent = thanks;

以上,就是抓下網址後,把結果提交到 Google 表單的方法,Google 表單收到提交後,就會把收到的結果寫進 Google Sheets 中。


Demo 頁、原始碼

Demo 頁跟原始碼都放在 GitHub 中了,大家取用前記得先對本篇點讚,或是對 GitHub 點星星。

Demo:https://letswritetw.github.io/google-sheets-edm-reply/edm.html

原始碼:https://github.com/letswritetw/google-sheets-edm-reply

點擊結果的 Google Sheets:https://docs.google.com/spreadsheets/d/1ws0AMXGlgjO4SNYm1J_0wi49yIsVpxOYIlXG2QQwt0g/edit?usp=sharing

Summary
用 Google Sheets 取得電子報中點擊回覆結果
Article Name
用 Google Sheets 取得電子報中點擊回覆結果
Description
本篇大綱:本篇要解決的問題。製作一份電子報。製作一個 Google 表單,抓需要的欄位值。建立回覆結果頁、抓取網址參數、把結果 POST 到 Google 表單、清掉網址上的 email 參數、不同的結果,顯示不同的文字。Demo 頁、原始碼。
Augustus
Let's Write
Let's Write
https://letswrite.tw/wp-content/uploads/2020/08/logo_512.jpg

隨選筆記文

Vue

Vue.js 3 Composition API 基本學習筆記-2:fetch data、export / import

Vue

用 VuePress 製作說明文件頁面 – 1:安裝

Front-End

File API 客製上傳檔案按鈕 / input file

Vue

Vue Transitions 製作簡單的 Carousel / Slider / 輪播功能

WordPress

WordPress 基本介紹

Bot LINE

LINE Notify:用 Google Apps Script 建立簡易網站監測機器人

Google Others

用 Firebase、Google Apps Script、GTM 自己做一個讚按鈕

Vue

用 VuePress 製作說明文件頁面 – 4:佈景主題、外掛

PWA

PWA 學習筆記 – 3:Workbox 參數

PWA

PWA 學習筆記-1:Cache、Workbox 基本使用

以下是留言,但關於留言的部份必需先讓你們知道:

本站的文章都是 August 因為覺得有趣,才會實作並整理成筆記文而後進行發表。

如果留言是希望把 Demo 改成「你想要」的樣子,或是把功能改成「符合你需求」的樣子,

Sorry~ 除非那修改是 August 也有興趣的,不然不會幫你們寫程式去面對工作或是交作業。

未來這類的留言不會再主動回覆。😎

另外,公開信箱是為了讓金流驗證用,

因為之前遇過幾次回信協助解決問題後,對方卻一聲謝謝也沒有,就這樣拿去幫工作交差。

因此決定不再回覆信件,有疑問就利用留言功能囉。