How To Send Google Doc Text to Google Sheets
Learn how to automate the process of sending text from a Google Doc to a specific cell in Google Sheets using Google Apps Script. This video guides you through creating a custom menu and writing the necessary functions to streamline your workflow.
So this video looks pretty terrible because we are going to be doing some automations. So basically the idea is I want to take a Google sheet, but I wanna update the text in a certain cell from a Google Doc. So here's the Google Doc. This is what it looks like. It's just gonna be some text, and I want to update this, A one on the tab called doc. So whatever your use case is for this, that you want to take a doc, update a sheet. Sell. This is what we're gonna do in this video. What we need to do, a couple things is we want to create a menu up here so that we can update this. There's some other ways we can do the automation, but I just wanna make it like a user will update it when they're ready.
So I'm gonna go up to Extensions app script in my Google. Here I'm going to create a function called on open. Now this is a built in function , so I don't need to do anything else except add the actual menu, which is variable UI equals document, app Do get ui, and then I'm gonna do ui, create menu. I'm gonna name this. Update. So I can name this automations, I can name this custom menu . I'm going to say update Google sheet actually. Uh, 'cause there's only gonna be one function in here, which is add item. And here we're going to name it, update my Google sheet, and then we're going to put the name of the function inside of here. So update Google sheet, all one word.
For the second part, the second one is down here, function update, Google Sheet, and we're gonna create a function down here. Last thing we need to do here is add to ui. Don't forget that this is easily forgettable and it essentially, we are going to save this. Close it and refresh the sheet so that it actually executes that on open. And if you didn't add that UI, it just wouldn't work. Here next to help. There'll be one. Let's see if there's a error . It says completed. There it is. Oh, just took a little bit of time, A little bit of moment. Next to help or accessibility will be update Google Sheet. If I click it, it says update my Google sheet. And if I go into my editor here of App Script , it will execute whatever is here.
So if I say variable, I, if I use this as well, L and I go UI alert, Hey, you clicked. And I save that. I don't, now that it's this function on open has triggered and it's happening. I just need to update this. And now if I click it, maybe you clicked. Cool. So it's working. I need to actually write the update now. Now I need to take the text from here and put it into here. So I need a variable which is going to be, let's make a global variable sheet equals and in quotes. This sheet, so I'm gonna say variable sheet equals spreadsheet app. Do I think we need to open by URL sheet? So I'm gonna put that in all caps there.
So we wanna get the tab, variable doc, tab equals sheet dot get sheet by name. And that's Doc. So you have your URL up here. You have the tab that we're looking at here, and I'm gonna go sheet. Do get range , the range , actually, sorry, we want to get doc tab, get range . So inside of that tab we're going to do. I can do a one, but what I like to do in app script is one comma one. This is row then column. Why that is is because then these numbers become programmable, meaning I can iterate through them, so I can do all the, an entire row and entire column . I can do a lot of much more interesting stuff than writing, just hard coding.
Essentially, we're talking about a one here, so one, one without quotes, set value. We wanna get all that doc stuff. So what's all this stuff? Well, let's create. A variable up here, doc stuff is equal to document app dot get active document do get body dot, get text. Now what is this? This is just all of the text that's inside of this Google Doc. So right now it's just gonna be say up update inside of the sheet, that text, and that's all the doc stuff and we're gonna set that value. So. Let's run this. Uh, I'm gonna run this from my doc. I'm gonna say update my Google sheet. It might ask me for authorization . Yes, it does. This will only be the very first time you run it, and if we go back to our sheet, it says, update inside of the sheet, which is this.
Check if it's updated. Please pray and hope it's okay. Can I make spaces or new lines more? Let's see. Update. Update my doc or sheet. There it is. Oh, and it has all of the new lines in there. Perfect. Isn't that cool? So we can take a Google Doc, update a Google sheet from that Google Doc at least. One particular cell. Hope you enjoyed that and fairly fun little quick function here with a sheet here and you learn also the on open function . Awesome. I. You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right
now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.