Create uniform formatting even if you're copy and pasting data that is differently formatted.
Academy ↓
Hey there stranger!
Sign up to get access.
Reformat Pasted Cells Automatically
About this Tutorial
Sheet Resources
Video Transcript
0:00 So a member had a very interesting question, and they said basically I don't like that when I cut something like Command X, and I paste it.
0:09 I have this blank cell here now This got me thinking about how to fix this and I realized this is not actually necessarily fixable I started trying to create a on edit function in Apps Script where I took the event that was happening Which is the pasting and found out like what is the format that it
0:31 should be. Um, and this doesn't solve the problem of cutting, but it does solve the problem of pasting. So I want to show you this interesting solution where we reformat pasted cells automatically.
0:45 Uh, and it solves a different problem. So my solution was not the solution to the first problem, but it is a solution to some problems.
0:52 So let's say we have two pieces of a sheet and we're copying and pasting data from one place to another, we're trying to like, organize the data in some way, but they're formatted in different ways.
1:06 So this has an orange background and it's also a different size, we can even make it super small. And we copy it, not cutting, but just copy.
1:14 And we want to paste it here to B4, but we don't want to paste, we could paste special values only, we could do that.
1:23 But sometimes we don't remember to do that, We just command V, but that if you just notice that it automatically reformatted it.
1:33 And so that's what this solution is, is reformatting the cells automatically when you paste. And actually when you do any edit, um what it does is, first thing is we get all of these four variables.
1:48 We need the row, the column, how many rows are in the edit range, how many columns are in the edit range.
1:53 And so we're only focusing on when there is only one row and one column, so that doesn't work if it's three columns.
2:00 It's high if you're copying and pasting or editing anything, uh that is more than one row one column. What we're doing is we're taking the existing format that's at the top of the column and we're going to copy that format, and just the format only.
2:18 If we are at the top of the column, we're going to get the second row. That's how we sort of figure it out.
2:23 So that's why there's two things here. Basically, if it's more than, if the row number is higher than two, then we're going to grab uh the second uh row.
2:35 But if it's actually row two, then we're going to get the third row. And that allows us now to be able to copy paste data somewhere, but automatically reformat it once we have uh pasted it.
2:50 So this is really fun. Again, this is uh if you don't remember to copy and paste values if you're trying to do something fast or you're trying to do just Command-C, Command-D.
3:01 Command-V. And you have these uh weirdly formatted things. Also, helps if I want to reformat this. So let's say I just want to increase the size.
3:13 Because that's an edit that will also work. You have to actually edit the value. So if we edit the value here, then it will edit.
3:23 It'll actually change the format again. So that keeps your data well formatted or at least the same format as you've had before, especially if you're copying and pasting from one place to another.
3:34 Actually, this will work the other way as well. If we paste here, it'll work here. Because it's working on every single active sheet.
3:43 If you're a BetterSheets member, you can get this on BetterSheets.co. Down below, you get this exact app script and this sheet.
3:50 If you're not a member yet, become a member today.
0:09 I have this blank cell here now This got me thinking about how to fix this and I realized this is not actually necessarily fixable I started trying to create a on edit function in Apps Script where I took the event that was happening Which is the pasting and found out like what is the format that it
0:31 should be. Um, and this doesn't solve the problem of cutting, but it does solve the problem of pasting. So I want to show you this interesting solution where we reformat pasted cells automatically.
0:45 Uh, and it solves a different problem. So my solution was not the solution to the first problem, but it is a solution to some problems.
0:52 So let's say we have two pieces of a sheet and we're copying and pasting data from one place to another, we're trying to like, organize the data in some way, but they're formatted in different ways.
1:06 So this has an orange background and it's also a different size, we can even make it super small. And we copy it, not cutting, but just copy.
1:14 And we want to paste it here to B4, but we don't want to paste, we could paste special values only, we could do that.
1:23 But sometimes we don't remember to do that, We just command V, but that if you just notice that it automatically reformatted it.
1:33 And so that's what this solution is, is reformatting the cells automatically when you paste. And actually when you do any edit, um what it does is, first thing is we get all of these four variables.
1:48 We need the row, the column, how many rows are in the edit range, how many columns are in the edit range.
1:53 And so we're only focusing on when there is only one row and one column, so that doesn't work if it's three columns.
2:00 It's high if you're copying and pasting or editing anything, uh that is more than one row one column. What we're doing is we're taking the existing format that's at the top of the column and we're going to copy that format, and just the format only.
2:18 If we are at the top of the column, we're going to get the second row. That's how we sort of figure it out.
2:23 So that's why there's two things here. Basically, if it's more than, if the row number is higher than two, then we're going to grab uh the second uh row.
2:35 But if it's actually row two, then we're going to get the third row. And that allows us now to be able to copy paste data somewhere, but automatically reformat it once we have uh pasted it.
2:50 So this is really fun. Again, this is uh if you don't remember to copy and paste values if you're trying to do something fast or you're trying to do just Command-C, Command-D.
3:01 Command-V. And you have these uh weirdly formatted things. Also, helps if I want to reformat this. So let's say I just want to increase the size.
3:13 Because that's an edit that will also work. You have to actually edit the value. So if we edit the value here, then it will edit.
3:23 It'll actually change the format again. So that keeps your data well formatted or at least the same format as you've had before, especially if you're copying and pasting from one place to another.
3:34 Actually, this will work the other way as well. If we paste here, it'll work here. Because it's working on every single active sheet.
3:43 If you're a BetterSheets member, you can get this on BetterSheets.co. Down below, you get this exact app script and this sheet.
3:50 If you're not a member yet, become a member today.