如何用Python和Google“自动化”我的婚礼?

2016年9月3日，对世界上的大多数人而言可能只是寻常无奇的一天，但对我而言，将永生难忘，因为在那一天我和我的爱人结婚了。

在规划婚礼时，要考虑许多不同的方面。食物、装饰、桌子装置（是的，这是独立于装饰的一个重要环节）、鲜花、住宿、交通、娱乐和位置选择等。虽然在规划婚礼时有许多未知数，但是我可以肯定一件事。在婚礼中，将会有很多的邀请名单、嵌套名单、以及其他更多相关名单等等。当我面对着越来越多的项目时，我开始怀疑是否有更好的方法来进行处理？如果一切都只是依赖手动，实在是太低效了，必须利用一些技术来进行改进。

你可能会感到惊讶，但是邀请人们参加婚礼是昂贵的（超过380磅），因为你需要发送“按时出席”卡片以及随后的关于婚礼细节的邀请。这个过程也是很慢的，因为你必须通过邮寄的方式来进行发送。追踪人们是否已经收到了邀请，以及他们是否想要参加提供免费食物和饮料的派对。最后，邀请卡也并不环保，因为它们都是被一次性使用，且容易丢失或放错。

回到名单问题。嘉宾名单分为几个部分：

1、你想要邀请的人的名单；

2、已经回复你的R.S.V.P邀请函的人的名单；

3、已经回复你会来的人的名单；

4、已经回复你会来且选择参加提供免费食物和饮料派对的人的名单；

注：R.S.V.P. 是法语短语“répondez,s’il vous pla?t”的缩写，英文意思大概等于Will youplease reply 汉语翻译成请尽快回答或请马上回答。“R.S.V.P.”的作用就是在于礼貌地提醒人们一个他们应该早就知道的道理：如果您收到请柬，就应该回复。

名单是好的，它们具有预定义的要求和响应，这使其成为自动化的重要选择。

瓶中信（Message In a Bottle）

不管处于何种年龄层，我确信婚礼名单上每个人都有手机，这意味着该是Twilio（一个做成开放插件的电话跟踪服务）发挥作用的时候了。如果你想要查看代码信息，你可以看看GitHub上的repo。

SMS完美的满足了我的需求。我可以配置发出群发短信，并快速有效地处理回应。在绘制一个MVP并且考虑数据库的时候，我想要一些易于分享的东西，不想浪费时间来构建视图。偶然发现的gspread python库，使我能够读写谷歌电子表格。虽然不是最快的选择，但它确实足够灵活，并提供了一个易于访问和可读的输出。

对于初始的R.S.V.P，我创建了一个包含以下信息的电子表格：

Name（名字）；

Telephone_number（电话号码）；

Confirmation_status（确认状态）；

Contact detail status（联系的具体状态）

Message_count （发送给客人的邮件数量，稍后会派上用场）

主要数据输入完成后，我使用gspread来遍历列表，并向每一个具有与之相关联的手机号码的客人发送短信：Sheets.py

import json
import time
import gspread
from oauth2client.client import SignedJwtAssertionCredentials
from twilio.rest import TwilioRestClient

# Message your attendees from a spreadsheet

# add file name for the json created for the spreadsheet
json_key = json.load(open('.json'))
scope = ['https://spreadsheets.google.com/feeds']

credentials = SignedJwtAssertionCredentials(json_key['client_email'],
 json_key['private_key'].encode(),
 scope)
gc = gspread.authorize(credentials)
wks = gc.open("wedding_guests") # add your workbook name here
wks_attendees = wks.get_worksheet(0) # attendees worksheet

ACCOUNT_SID = 'TWILIO_ACCOUNT_SID'
AUTH_TOKEN = 'TWILIO_AUTH_TOKEN'

client = TwilioRestClient(ACCOUNT_SID, AUTH_TOKEN)

# to iterate between guests, amend this based on your total
for num in range(2, 60):
 print "sleeping for 2 seconds"
 time.sleep(2) # adding a delay to avoid filtering

 guest_number = wks_attendees.acell('B'+str(num)).value
 guest_name = wks_attendees.acell('A'+str(num)).value
 Message_body = <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2B50</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2764</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2B50</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2764</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2B50</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2764</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2B50</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2764</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-pds">"</span><span class="pl-cce">\n\n</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2709</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span><span class="pl-s"><span class="pl-pds">"</span> Save the date! <span class="pl-pds">"</span></span><span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2709</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span><span class="pl-s"><span class="pl-pds">"</span><span class="pl-cce">\n\n</span>Lauren Pang and Thomas Curtis are delighted to invite you to our wedding.<span class="pl-cce">\n\n</span>Saturday 3rd September 2016. <span class="pl-cce">\n\n</span>Colville Hall,<span class="pl-cce">\n</span>Chelmsford Road,<span class="pl-cce">\n</span>White Roding,<span class="pl-cce">\n</span>CM6 1RQ.<span class="pl-cce">\n\n</span>The Ceremony begins at 2pm.<span class="pl-cce">\n\n</span>More details will follow shortly!<span class="pl-cce">\n\n</span>Please text YES if you are saving the date and can join us or text NO if sadly, you won't be able to be with us.<span class="pl-cce">\n\n</span><span class="pl-pds">"</span></span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2B50</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2764</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2B50</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2764</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2B50</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2764</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2B50</span><span class="pl-pds">"</span></span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-k">u</span><span class="pl-pds">"</span><span class="pl-cce">\u2764</span><span class="pl-pds">"</span></span>,
 if not guest_number: # No mobile number skip this guest
 print guest_name + ' telephone number empty not messaging'
 wks_attendees.update_acell('E'+str(num), '0') # set number to 0

 else:
 print 'Sending message to ' + guest_name
 client.messages.create(
 to="+" + guest_number, # add the + back to make the number e.164
 from_="", # your twilio number here
 body=message_body,
 )
 wks_attendees.update_acell('E'+str(num), int(wks_attendees.acell('E'+str(num)).value) + 1) # increment the message count row
else: # else part of the loop
 print 'finished'

因为短信可以看起来很简单，所以我添加了一些unicode来增加趣味。下面是幸运的受邀者接收到的短信模版：

接下来，我使用Flask作为我的web服务器，并将我的Twilio消息请求URL指向/messages url并创建简单的if语句来解析回复 (yes,，no)：hello_guest.py

@app.route("/messages", methods=['GET', 'POST'])def hello_guest(): if "yes" in body_strip:
 # We have a keeper! Find the attendee and update their confirmation_status wks_attendees.update_acell("F"+str(guest_confirmation_cell.row), 'Accepted') # update the status to accepted for that guest resp.message(u"\u2665" + "Thanks for confirming, we'll be in touch!" + u"\u2665") # respond to the guest with a confirmation! elif "no" in from_body.lower():
 # update the confirmation_status row to declined for that guest wks_attendees.update_acell("F"+str(guest_confirmation_cell.row), 'Declined')
 # respond to the user confirming the action resp.message("Sorry to hear that, we still love you though!")

 else: # respond with invalid keyword resp.message("You sent a different keyword, we need a yes or a no, you sent: "+ 
 from_body)
 return str(resp)

第一条消息是在2月19日上午8:37的时候发出的，3分钟后（即上午8:40）收到了第一条确认短信。上午9:38，我已经收到了23条确认回复，已经达到32%的接受率！初始群发短信两天后，我们收到了58%的客人的确认信息！尽管取得了明显的成功，但我的未婚妻并不完全青睐我作为婚礼邀请服务（SAAWIS?）的短信，所以，我决定在我的应用程序中添加一些其他功能。

这个功能就是统计！我可以计算现场出席名单并按要求退回，给予新娘关于客户名单是如何塑造的即时反馈。代码很简单，因为我已经在电子表格中设置了一些基本的计数器，因此剩下的只是抓取这些单元格的内容，并将其添加到短信中的事情：hello_guest.py

# attendance variablesguest_confirmed = wks_attendees.acell('C70').valueguest_unconfirmed = wks_attendees.acell('C71').valueguest_no_response = wks_attendees.acell('C72').valueguest_acceptance = wks_attendees.acell('C73').valueelif "numbers" in from_body.lower():
 # return statistics (total guests, food choices list) resp.message("R.S.V.P update:\n\nTotal Accepted: " + guest_confirmed 
 "\n\nTotal declined: " guest_unconfirmed "\n\nTotal no response: "+ 
 guest_no_response + "\n\nTotal acceptance rate: " + guest_acceptance)

以下是最终的短信，不是很漂亮，但很有用：

现在Lauren可以即时跟踪出席率的情况，这大大缓解了她的压力。从那时起，所有的事情都通过系统来运作，SMS被尽可能多的融入到婚礼的方方面面。有些是显而易见的，例如当婚礼网站上线时，发送通知短信，分享礼物列表以及其他我至今仍然感到骄傲的事情。

食物环节

在建立R.S.V.P名单之后，经常被推迟的部分是让客人确认他们的食物选择。你可能会惊讶，让人们选择免费的食物是能有多困难。第一步是发送另一条短信，告诉那些确认参加婚礼的客人访问网站，并通过一个谷歌表单选择他们喜欢的食物。相当标准的流程，然而，表单被设置为填充与参与者相同的工作簿。这意味着，我现在已经有接受邀请客人的电子表格，以及那些填写了食物选择表格的客人名单。通常我会等待客人慢慢选择他们的饭菜，但因为我的婚礼是由Twilio驱动的，也就意味着我可以用最少的努力来跟踪嘉宾状态。

数据需要匹配访客名称上的两个电子表格，并且在有匹配的时候更新客人的食物选择状态。这需要一些额外的工作，但一旦重排代码，我就可以按需批量运行脚本，并通过短信获取我的客人的最新状态：food.py

import jsonimport timeimport gspreadfrom oauth2client.client import SignedJwtAssertionCredentialsfrom twilio.rest import TwilioRestClient# add file name for the json created for the spread sheetjson_key = json.load(open(''))
scope = ['https://spreadsheets.google.com/feeds']

credentials = SignedJwtAssertionCredentials(json_key['client_email'],
 json_key['private_key'].encode(),
 scope)
gc = gspread.authorize(credentials)
wks = gc.open("") # add your spreadsheet name herewks_attendees = wks.get_worksheet(0) # attendees worksheetwks_food = wks.get_worksheet(1) # food responses worksheetACCOUNT_SID = 'TWILIO_ACCOUNT_SID'AUTH_TOKEN = 'TWILIO_AUTH_TOKEN'client = TwilioRestClient(ACCOUNT_SID, AUTH_TOKEN)# to iterate between 10 to 60 manual hack to ensure no guests not left outfor num in range(2, 60):
 food_guest_name = wks_food.acell('B'+str(num)).value # food choice name column if food_guest_name:
 attendees_name = wks_attendees.find(val_food_guest_name).value
 attendees_name_row = wks_attendees.find(val_food_guest_name).row
 menu_status = wks_attendees.acell("G"+str(attendees_name_row)).value

 if food_guest_name == attendees_name:
 print if menu_status == 'Y': # data already matched, move on print('Skipping')

 else: # user has supplied their choices, update main spreadsheet print ('Food sheet name ' + food_guest_name + 'Attendees sheet name ' + attendees_name)
 # update menu choices row wks_attendees.update_acell("G"+str(attendees_name_row), 'Y')
 else:
 print('nothing found, moving on')
 wks_attendees.update_acell('E'+str(num), int(wks.acell('E'+str(num)).value) + 1) # increment the message count row else:
 # send message to the admin that the process has been completed with update stats client.messages.create(from_="", # twilio number here to="", # admin number here body="Finished processing current meal listnnGuest meals confirmed" + guest_meals_confirmed + "\n\nGuest meals unconfirmed: " + guest_meals_unconfirmed)

现在有一个确认的客人名单和越来越多的食物选择名单，通过主要应用程序将这些统计数据公开是有意义的。所有需要做的就是抓取相关单元格的内容，并用短信回复:Hello_guest.py

# respond with the current food totals and the meal choices
elif "food" in body_strip.strip():
 resp.message("Guest meals decided:" + guest_meals_confirmed +
 "\nGuest meals undecided: " + guest_meals_unconfirmed +
 "\n\nMenu breakdown:\n\n" + starter_option_1 +": " +
 starter_option_1_amount + "\n" + starter_option_2 +": " +
 starter_option_2_amount + "\n" + starter_option_3 +": " +
 starter_option_3_amount + "\n" + main_option_1 +": " +
 main_option_1_amount + "\n" + main_option_2 +": " + main_option_2_amount +
 "\n" + main_option_3 +": " + main_option_3_amount + "\n" +
 dessert_option_1 + ": " + dessert_option_1_amount + "\n" + dessert_option_2
 + ": " + dessert_option_2_amount)

让婚礼餐饮者了解我们的进展，并提供有关谁没有选择的可操作数据是非常方便的。追踪客人是另一个自动化选择。简单地遍历参加者名单，找到没有选择用餐选项的顽皮客人，并向其发送信息！Chase.py

for num in range(2, 72): # manual hack to ensure no guests not left out print "sleeping for 3 seconds" time.sleep(3) # adding a delay to avoid carrier filtering wedding_guest_number = wks_attendees.acell('B'+str(num)).value # grab attendee tel number wedding_guest_name = wks_attendees.acell('A'+str(num)).value # grab attendee name menu_guest = wks_attendees.acell('G'+str(num)).value

 if not wedding_guest_number:
 print wedding_guest_name+' telephone number empty not messaging' # output to console that we are not messaging this guest due to lack of telephone number wks_attendees.update_acell('H'+str(num), '1') # increment the message count row for the individual user else:
 if menu_guest == "N": # guest has not chosen food! CHASE THEM! print 'Sending message to '+wedding_guest_name
 client.messages.create(
 to="+" + wedding_guest_number,
 from_="", # your Twilio number here body="If you have received this message, you have not chosen your food options for Tom & Lauren's Wedding!\n\nYou can pick your choices via the website, no paper or postage required!\n\nhttp://www.yourwebsitehere.com/food" )
 wks_attendees.update_acell('H'+str(num), int(wks_attendees.acell('H'+str(num)).value) + 1) # increment the message count row for the individual userelse: # else part of the loop print 'finished'

大婚的日子来得比我们想象的还要快。现在唯一需要做的事就是发送最后一条短信，提醒客人基本的细节，并提醒他们带把伞以防碰上一般的英国夏季的雨季：

总结

婚礼永远不是一件简单的事情，它会让你感到很多事情都不在你掌控之中。自动化让我的生活变得更加轻松，它可以提供与我们的客人进行沟通的直接渠道，而且我通过各种不同的方式来跟踪、推动以及回应客人。它帮助我们在婚礼的时间消耗方面变得积极主动，让我们可以空出来时间去关注大日子的其他重要领域。

构建复杂问题的可扩展解决方案从来都不是件容易的事，即使在最终形式下，我的应用程序有时也会很脆弱。我已经计划建立一个更为完整的解决方案，具有进度的数据可视化、语音集成，以及更少的依赖CLI脚本等。总的来说，我很满意整个自动化的过程，没有通讯系统是完美的。你需要实施最适合你的受众的渠道，无论是短信、语音、聊天、视频还是信号量等。