How do I reference a different spreadsheet in Apps Script?

Intermediate

About this Tutorial

I'll show you how to get text from one spreadsheet file to another with Apps Script. Yes you can do this by Importrange() but you might want another way to do it.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

IMPORTRANGE()

Video Transcript

<div>0:01 A member of BetterSheets asked this question, uh Sharon asked this question actually, if we have a first sheet and a second sheet, how do we get information from one sheet to the other without import range but with actually with Apps Script ? 0:13 Uh because absolutely we can get whatever is in a first spreadsheet , let's say this one says hi there, and then the second spreadsheet over here on the second tab, we want to do uh get that in a whatever is in a one. 0:25 So we'll do equals import range , and we need the spreadsheet URL, in this case the here, put that there.

We need the range string which is going to be sheet one exclamation End Quotes. 0:40 And we get the information. We have to allow access , but then we actually get whatever is there. We can say hello, we can say hello there, and in the second sheet it'll update there. 0:54 Cool, but how do we do this with uh Apps Script ? And here's the answer here.

Uhm, we're gonna use a function , get first AI, A1 AI, we're gonna get the text which is spreadsheetApp .openByURL, and we're gonna put the URL in there. 1:13 Actually, let me walk through this whole thing because I think we can walk through this much simpler than just explaining it. 1:20 So what we're gonna need to do first is have some place to get it and some place to put it. 1:26 So where we're gonna put the information is gonna be sheet1, A1 in the second spreadsheet .

Again, we're gonna go up to extensions, Apps Script , and we're gonna open this file. 1:36 We're gonna write some function name, we'll call it getFirstAI. We need to put the information somewhere, but let's create a variable text equals , we will have some information there. 1:47 But how do we get that text into this second spreadsheet ?

Is we're gonna do spreadsheetApp .getActiveSpreadsheet.getSheetByName We're going to do Sheet1, getRange , a1, because we want to put a1 in a1, and then we're gonna do setValue . 2:12 And why I put this variable text up here is because this variable down here is gonna be text. Whatever output we get from here, we're gonna set that value. 2:20 We're gonna do this all with abstract here. So what is this value up here?

We're gonna go back to the first spreadsheet , get that URL again, and we're gonna do SpreadsheetApp . 2:40 We're gonna paste the URL in there inside of quotes, and then we're gonna do pretty much the same thing. We're gonna do getRange , sorry, not getRange , getSheetByName, because now everything before this first dot like this, getSheetByName, getRange , setValue , or getValue , is what we're gonna do. 3:02 But before that is different.

But after it, it's gonna treat this sheet the same as if we're opening the active spreadsheet . 3:11 So we're gonna do sheet1, And instead of setValue , we're going to getValue . Now we're gonna save this, and over in our second sheet actually, I'm gonna delete that import range . 3:28 We're gonna have nothing there. We're gonna have hello world in A1 on our first spreadsheet . And this is the URL that we're grabbing. 3:36 This is the sheet name down here, sheet1. We can change that later, and I'll show you how.

But all we're gonna do is hit run. 3:43 When you do this for the very first time, you are going to have to accept the permissions, or set the permissions. 3:48 It says execution started, execution completed. Let's look at our second sheet, and we have hello world. And so if we update this, let's say we have some more information here. 3:58 It's not going to automatically update this. We will have to come back here and click run again.

But, watch some other videos I have, and you can set up triggers to do this. 4:08 Instead of clicking run every single time, you can set up some triggers in order to do this every day, or based on some thing you do in the sheet. 4:16 There it is, more information. I want to show you some issues you might have with this. Let's say we do not get the entire URL. 4:27 Maybe we want to edit this URL in some way. Let's do just this, and see if this runs. If we edited that URL, hit run, and we have invalid argument URL. 4:38 See, this is the URL. If we use, this URL.

Let's use it, and just go to it. See what happens. 4:45 It takes us to the spreadsheet , but for some reason it doesn't have this just slash edit slash or hashtag GID, but maybe it is just the last slash we need. 5:00 Let's see. Let's add a last slash, command s to save that update, hit run again, and it's fine.

Right, so it's not the entire URL we need, but we do need up and to and including that last slash, so we don't need this edit, we don't need a GID, Peace. 5:20 But we do need the URL, but there's another thing we can do here, what if we don't want the entire URL, we can use this ID, which is inside of the URL, let's see what we can do here, instead of getFirstA1, let's say getA2, and instead of openByURL, we're going to replace this with openByID, and let's 5:48 see if just this ID works, and again the ID we're using is this one cue, right here, this text in between in the D slash and the last, the edit. 5:59 We're gonna go over here.

Here, put it in quotes, hit command s, let's put something in A2, do we get this, and also let's change this to A2 where we're gonna go, let's save it all, we're gonna select A2. 6:18 Hit record . Run and see if that works, and it works, actually we got A1, sorry, we needed to change A2 as well, so we need to change both ranges . 6:32 Let's run that again, double check it works, and it does.

So can we use this function in the inside of a sheet, just like we did with import range . 6:43 Let's see, get first A1, we're going to hit enter, and we're going to get an error , and it says you do not have permission to call, and it gives us the required permissions, this is Google APIs, this is line 3, but this is rather weird, right? 6:56 What this is, what this is literally saying is that, yes, we Do not.

have the permissions, but what is really happening in the background is that this type of function doesn't work inside of a function inside of a cell, so basically that cell doesn't have permission, whereas our function was and is working 7:18 perfectly from Apps Script and within Apps Script , but if we put that function inside of a cell, that cell doesn't really have permission, so it's getting sort of in the way, and it's a little bit daunting to see this kind of uhm error , and if you're getting this error, you're probably going to be watching 7:35 this video if you get this uhm required permissions, Google APIs, or whatever.

Or this exception, you do not have permission to call SpreadsheetApp .openByUrl. 7:49 Ah, but again, the fix is, just use it in Apps Script , and run it with some trigger , set this value here, sometimes you may try to write this with like return, text, and that's how a custom function usually works, but we don't need a custom function in this case, we can just set, get the information, 8:07 and set it using this SpreadsheetApp .getActiveSpreadsheet.getSheetByName, get range , and set value.

And this is the trick here, to setting the value inside the function , instead of doing it through the through a custom function , like this. 8:22 But we do, we can get this, and I'm really excited you watched this whole video. If you want to see more, uh, Apps Script , uh, check out my other, uh, courses, we have Spreadsheet Automation 101, we have Learn to Code, there's we have Master Spreadsheet Automation on Udemy, if you want to see the entire 8:43 20-hour course, goes really deep into APIs and all that stuff.

Um, but yeah, I'm really excited, I hope this has helped you a lot, Sharon especially.</div>