Automatically export Google Forms to Notion using IFTTT and Django

Good day to all! I think this article will be interesting to everyone who uses Notion, but for some reason could not move to it completely.

Prehistory


I am developing my project . On the landing page after entering the email, a link to a social survey based on Google Forms is issued. Responses are recorded in the nameplate on Google Drive.

The problem is that all I carry with me I keep in Notion. It is corny more convenient. Handled by copy-paste, while there were few reviews. Then there were more of them - and it was necessary to come up with something. Who cares what happened - welcome to cat.

Problem


Google Forms record answers only in the nameplate - that is, there is no other recipe here. Therefore, I had a plan: let's listen through IFTTT to updates of the tablets, send new data to the webhook, somehow process them and upload them to Notion.

For those who are not familiar with IFTTT: this is a service that allows you to make chains of actions. Say, “the post came in telegrams” - “we export it on VKontakte”.

The plan began to fail: Notion does not have an official API. But someone reversed it and made an unofficial API .

image

The final plan was this:

  • We make the applet in IFTTT: “A line has been added to the tablet - send it to the server
  • We make directly the server that receives data and sends it to Notion

The second problem appeared when it turned out that IFTTT had broken integration with Google Sheets, and therefore the applet did not work.

image

Therefore, I had to change the plan: we pump out the csv'shku with Google Sheets, parse it on the server and throw everything new in Notion. IFTTT is used as a trigger for the whole process.

Part 1. CSV with Google Sheets


This part is perhaps the easiest. We open the table for viewing (so that you do not have to bother with cookies). Next, take and copy the link to export CSV. To do this, simply press Ctrl on the keyboard and type Ctrl + Shift + J (that is, open the developer’s console), go to the Network tab. Then click on File - Download - CSV. We see the request and copy the link.

Part 2. Writing a server


Since we have a Python library, we will write in Django.

Now a little about the structure of specifically my table. A table in Notion, unlike a table in Google Sheets, has a “Reference” column. This is a link to another table (in my case, a description of the functions that users liked). The rest is generally clear: just columns with just data.

We go to Notion, we already familiar with Ctrl + Shift + J open the console, go to Application - Cookies, copy token_v2 and call it TOKEN. Then we go to the page we need with the plate and copy the link to it. Call NOTION. If you also have Relation, go to the page with Relation, copy the link and call, for example, NOTION_FUNCTIONS

Next, write the following code (pre-import notion):

def index(request):
    if request.method == "POST":
        client = NotionClient(token_v2=TOKEN)
        database = client.get_collection_view(NOTION)
        current_rows = database.default_query().execute()
        database_functions = client.get_collection_view(NOTION_FUNCTIONS)
        current_rows_functions = database_functions.default_query().execute()

In it we connect NotionClient, we say „Databases? Give two! “And we get the data directly from these two plates (by default request, but it is possible with sorting, for more details, see the documentation for the library).

Then we must do the following: request a CSV from Google and parse it. We will do it pandas'om.

result = requests.get(SHEET).content
pandas_result = pd.read_csv(io.StringIO(result.decode('utf-8')))
timestamps = pandas_result[["Отметка времени"]].values
ages = pandas_result[["Ваш возраст"]].values
sexes = pandas_result[["Ваш пол"]].values
cities = pandas_result[["Ваш город"]].values
socials = pandas_result[["Ссылка на соцсеть (просто чтобы проанализировать получше)"]].values
agreements = pandas_result[["Можно ли вам написать, если есть какой-то вопрос."]].values
control_usages = pandas_result[["Какие примеры из области управления вас заинтересовали"]].values
health_usages = pandas_result[["Какие примеры использования из области здоровья вас  заинтересовали"]].values
prices = pandas_result[["За какую цену вы бы готовы были купить устройство. Можно с пояснением :)"]].values
mentions = pandas_result[["Предложения, замечания, негативные моменты по лендингу или в целом"]].values

Then we have to go through all the data from this plate and check whether they are added to Notion or not yet. To do this, we requested data from the plates.

def checkTimestamp(rows, timestamp):
    for i in range(0, len(rows)):
        row = rows[i]
        if row.name == timestamp:
            return True
    return False

Separately, it is worth mentioning about “row.name”, because an attentive reader will probably ask: what is it?

This is the name of the column in Notion (where recording times are stored). I somehow didn’t manage to add with Russian names, so I changed all the names to English and add them.

image

And now the code to verify the data and add a line to the Notion label:

for i in range(0, len(timestamps)):
     if not checkTimestamp(current_rows, timestamps[i]):
           row = database.collection.add_row()
           health_usage = health_usages[i][0]
           control_usage = control_usages[i][0]
           ticks = health_usage + "," + control_usage
           row.title = timestamps[i][0]
           row.age = ages[i][0]
           row.sex = sexes[i][0]
           row.social_network = checkEmptiness(socials[i][0])
           row.can_we_write_you = checkEmptiness(agreements[i][0])
           row.city = checkEmptiness(cities[i][0])
           row.controlling_examples = checkEmptiness(control_usages[i][0])
           row.health_examples = checkEmptiness(health_usages[i][0])
           row.cost = checkEmptiness(prices[i][0])
           row.noticements = checkEmptiness(mentions[i][0])
           row.castdev_relation = findIds(current_rows_functions, ticks)

checkEmptiness is a function that checks if a null item has been passed to it. Notion somehow reluctantly worked when I fed him zero fields, so it's worth writing.

Now let's move on to the analysis of Relations, because in the official documentation I did not see about it. To make a link to a line from another database, you need to take it (of this line) and transfer it. Accordingly, if an array of links to strings from another plate is implied, you need to take an array of their identifiers. I personally added Relations by function names.

def findIds(current_rows, titles):
    print("titles", titles)
    print("current rows", current_rows)
    array = []
    for a in range(0, len(current_rows)):
        if current_rows[a].name in titles:
            array.append(current_rows[a].id)
    print("Ids", array)
    return array

At the end, after creating the lines, we add the answer so that at the other end they know that the request has arrived.

return HttpResponse("Hello, habr.")

Taschemta with the most important server is finished, go to IFTTT.

Part 3. IFTTT


Go to the applet creation tab . We select the trigger (in our case it is Date & time), set “every hour”. We select the triggered (that is, “that”) Webhook, specify our (so far) local address in order to test it. Well, that’s it. Test.

Part 4. Heroku


You thought what we were messing with with this trigger from the IFTTT - this is not to pay. Heroku offers a free rate for hosting our stuff. The main thing is that the service sleeps at least 6 hours. And he will definitely sleep, because we call him to work every hour, and not every minute.

image

Further we do the following. Go to heroku to create a new project . Next, install their client on their operating system . And then we do everything according to the instructions that appeared after creating the application.

After downloading everything on heroku, go to our applet and edit the URL to a new one.

Now the list should be updated every hour. Hypothetically, IFTTT may give an error that you have some kind of long request, but this is not so important.

Update


It turned out to be important. When the IFTTT catches persistent errors, it begins to skip applets.
To solve this problem, just start a new thread for this whole stuff, immediately giving the answer.
if request.method == "POST":
   thread = Thread(target=run_notion_import)
   thread.start()
   return HttpResponse("Hello, habr.")


Another idea that I forgot to voice in the article is to check for nullity using the standard pandas method.
That is, your check will look something like this:
if not pd.isna(health_usages[i][0]):
   row.health_examples = health_usages[i][0]

Also popular now: