Google Workspace Studio: Extract PDF Data to Sheets Automatically

Key Takeaways

In this video, I'm going to show you how to build an automation in Google Workspace Studio that extracts data from a PDF and writes that data to a Google sheet. The quickest way of creating an automation or flow as it's called in Workspace Studio [music] is by telling Gemini what you want to automate so that it can build the flow for you. [music] Access the Workspace Studio discover page by either typing the URL in your browser or via the Studio icon in Gmail. Then type in the following prompt. For incoming emails that contain an attachment. [music] If the attachment is a PDF, add it to Google Drive. Then extract date, supplier, invoice number, and amount from the attachment you stored in Drive. And lastly, [music] write this extracted data into a Google Sheets. As you probably noticed, based on the data we want to extract [music] from the PDF, this specific prompt is for Gemini to build an invoice [music] assistant. If you want to build a flow that processes another type of document, like a resume, a financial report, or anything else, you can simply adjust this prompt to your document. Gemini has created a multi-step flow for us, which we can now edit and configure to our needs. If you're following along and the flow Gemini built looks different from mine, it's because Gemini generates a flow in the moment each time you ask it to do so, and it does not rely on predefined flows. We see that the starter step when I get an email is set up for the flow to run when we receive an email that has an attachment. The decide step contains the prompt for Gemini to assess if the attachment is a PDF. The outcome of this step will be either true or false. Sometimes we might have to tweak the prompt Gemini suggested when it created the flow. For example, the decide step works the best for this use case with the following prompt. Does the email contain an attachment of type PDF, email attachment, [music] and then the email attachment file type variable? Variables are placeholders for information from starters and previous steps that can be used to pass information to later steps. I suggest you check out the video that's linked on the top right corner of the screen right about now to learn more about variables in Workspace Studio. [music] The check if step is what will be used to continue the flow only if the outcome of the previous step is true. This means that the flow will continue only if Gemini confirmed in the previous step that the attachment is indeed a PDF. If the attachment is for example a JPEG file, the flow will stop at this step. You can view the reasoning for Gemini's decision in the activity [music] log. Step four is where the attachment is saved to Google Drive. Here we select the folder where the PDF should be saved to and we select the Gmail attachment as the variable. In the extract [music] step, we see that the content Gemini will extract the data from is the attachment that's stored in my drive. Remember in our initial prompt, we told Gemini that we wanted to extract four things from the PDF file. [music] Supplier, invoice number, amount, and date. In the extract step, we see that Gemini will identify those four pieces of information per our initial prompt. The last step, add a row, needs a bit of configuring. We start out by defining the spreadsheet we want the data to be written to. As of the making of this video, you can only add a road to a Google sheet stored on your Myrive. Writing to files stored on share drives will be enabled in the future, though. Gemini automatically selects a sheet in the furthest left position of your spreadsheet. For our flow, that's just fine. But if your spreadsheet contains multiple sheets, you might have to change the sheet selection. Gemini has identified four columns in our sheet and we can now map the extracted data from the PDF to [music] these columns by selecting the corresponding variables. Okay, our flow is set up. We are ready to test it. We're going to select an email for the test run that contains exactly the type of file we want our automation to run on in the future. An invoice in the form of a PDF file. We can see how the flow progresses step by step. Great. [music] The test was successful. We can now turn on the flow. For our use case, the flow is perfect. But [music] what if your use case is different? How can you adapt this flow to your needs? [music] You can be more specific in defining when the flow runs. For example, you could use the has the words field to limit this flow to emails with the specified words or phrases. [music] and the decide step. You could also use other variables like the attachment file name for Gemini to decide if the flow should continue. [music] Let me know in the comments section below. What automation do you want to build using Workspace Studio? [music] I highly recommend you watch the video on screen where you'll learn more about Workspace Studio. Also, subscribe to this YouTube channel for more such tutorial videos.