Copy That, From One Sheet to Another - Learn to Code in Google Sheets Part 4

Advanced

About this Tutorial

Learn to code in sheets. Automate Your Daily Sales Report with Google Sheets: Learn how to create a script that copies data from one sheet to another, including timestamps. Boost your productivity with this easy guide!

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

NOW()

Video Transcript

<div>0:00 All right, here's part four. We're gonna copy something from one sheet to another. So far we've been writing an app script , little things that take, do something right. 0:11 And now for the next video to prepare for the next video, we wanna create a script that runs by itself at any time. 0:19 It can run any, and it's gonna happen every single day.

So we're gonna create this scenario where we have a sheet called sales summary and we have a sheet called Sales Log. 0:33 Now we use, we'll use this sales summary in a one and every day we're gonna be having the total sales of that day right there. 0:41 But we wanna log it here every single day automatically. Now we're gonna create the trigger . Tomorrow triggers a very interesting word, but today we're going to write the script. 0:56 But just before we do the script, let's do one thing and just do what we want it to do.

We wanna take what's on sales summary, A one. 1:05 We're gonna copy that. We want it in the A column to paste the values no matter what. If we have a formula there or anything, we just want the value. 1:14 And here we want something like now, like a timestamp . So I'm gonna copy this and paste the value. Now this could be just formatted in another way. 1:23 Number date time, right? So this is the end result.

And then the next day on, we want whatever sales we had that day in that same spot we wanted to copy here. 1:37 So there's a few things we have to do, but first let's just jump into our up script and see what's going on. 1:43 So we have all of our script from before that we did. Let's just move that all down. We're gonna write a new function . 1:50 Again, this function needs to happen every single day. We wanna call it copy sales data , sales data to sales log. 1:59 That's just the name of it. We can really name it anything we want.

We're gonna create the curly brackets, we've got the parenthesis, the curly brackets, and inside we're gonna write some comments . 2:11 These are two slashes and then some text. And this gives us the ability to write in real words to ourselves notes as how do we want to break this down? 2:22 We want to get the data from sheet sales summary which in a one we want to copy it to sales log, whatever is the last row. 2:46 And so what we can do is we can probably just Google for this kind of stuff.

If we don't know the exact functions , we could probably just type in Google Script and copy two or copy two a sheet. 2:57 And what we're gonna find is we're gonna have the copy two function and it needs a destination and it has some options that we can put into it, into that function . 3:07 And we also want to paste values , which we found out if you did that. Google search is the option of content only true. 3:21 And then once once we have this paced values , right? And we have a timestamp , we will also want a timestamp in column call. 3:38 How do you spell Callum? Lum B.

So we can save this text. This doesn't do anything, literally doesn't do anything using this slashes comments out. 3:50 But first, let's just get a variable data . And we know this from the last video. Spreadsheet app dot get active spreadsheet . 4:00 Remember the parentheses get sheet by name. The sheet is going to be sales summary dot get range . The range is a one. 4:11 Get value. That's gonna get us the data . We want to get the sales log.

So variable sales, well actually we can just call it destination sheet equals spreadsheet . 4:27 App dot get active spreadsheet dot get sheet by name. And here we want sales log. So one thing we could do a last row function , find last row in sort of under that. 4:42 But actually we want it to insert into the first row. We want it in reverse chronological order after inserting a row. 4:50 So we can go to destination sheet dot insert row before the first row. Then we can say that we want to copy two. 5:11 We can find copy two actually here. And let's look at it.

Let's actually look at this one. Copy, two piece type. 5:23 Now let's just use this one. So we need a range . We need to know the range , not the values , but the range . 5:29 And we copy it to where it goes to. Cool. That's easy. We know what we're doing here. We're getting this data . 5:40 We actually don't even need get value, we just need that range . Copy two destination is gonna be this destination sheet. 5:52 In this case, we do need a range get range. We want it row one, column one. And then we want to put in this, in the options content's only. 6:07 True.

We only want the number . And for the timestamp , we do variable timestamp equals new date. And then all we need to do is spreadsheet , app dot, get active spreadsheet , Parentes, get sheet by name. 6:26 Sales log dot get range is gonna be one, not sorry, call row one column two, set value. And it's gonna be tap stamp. 6:50 So if we go up to our log up here or to run, we wanna run this and just test it out. 6:57 So run, gotta get some authorization . We have to do this each time when we create a new spreadsheet app.

Alright, let's see what happened. 7:08 We have this sales summary here of sales log here. Perfect. So it inserted a row and it puts in the timestamp . 7:16 Perfect. So a little bit of meta talking here about programming inside of Google Sheets and Google Script and app Script is if you have some data , like let's say this is incorrect, right? 7:30 We, we typed in a three and we didn't know. We're like, why is this sort of working but not working? 7:37 It's completing. We can always do logger dot log and look at the data .

And what this allows us to do is run it and see right here, we got this range . 7:46 Okay, we gotta just get range . So what does that get value? Let's run it and see what that value is and we can just log it in. 8:00 It's blank. There's nothing there. But if we do a one, ah, we have that mistake. Run it. We have, oh, the number . 8:08 Perfect. So our mistake was just in that range . This particular logger kind of thing is available to us, and we can add anything we want. 8:17 We can add a little string here and say data from a one. And then the plus sign.

Now when we let's save it first and run it, it will show up in our log data from a one and it'll add concatenate or combine that string to the answer of data to get value. 8:40 So in this video, we coded up a little gathering of data . We're inserting it into, we're inserting a new row into a new sheet. 8:50 And we're gathering all this data in every time we run this script. But how do we run this script every day without our coming here and running this script? 9:00 I'm gonna show you that in the next video.</div>