By clicking button, import data from another book Spreadsheets

[fix falcon]

Greetings dear members of the forum, after having searched all the different forums of this community I see myself in the need to go to you to help me in the correction of the following script.

I have two books Spreadsheets in googleDrive, the one is the source of the information called "Master" and the other book is the "Receiver". In the Receiver I have put a button that I want to do with a scrpit when I click, the information of B2 of the Master book is displayed in cell C2

The script that I have put is the following

function QuestionImport() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var hoja = ss.getSheets();

var Pregunta = query(IMPORTRANGE("Here my Spreadsheet key";"Unid1T1!B2"))

var actualizar = hoja.getRange("C2").setValue(Pregunta);

};

I appreciate your kind support

[Michael O'Shaughnessy]

Hello,

The "=QUERY()" function is a spreadsheet function and will not work in a script.  You can try to do something like this:

let master = SpreadsheetApp.getSpreadsheetById([ID OF THE MASTER]).getSheetByName("Name of sheet with value");

let masterCell = master.getRange("B2").getValue();

let receiver = SpreadsheetApp.getActiveSpreadsheet();

receiver.getRange("C2").setValue(masterCell);

NOTE: this was just typed out in the message... I apologize for any spelling errors!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a4a673dd-5e5c-4f9c-8f3c-2075cf4fdaa2%40googlegroups.com.

[Kim Nilsson]

If a Query is a must someone wrote a wrapper for it.

The person has a different username in this forum.

Here he is called Dimu Designs.

https://stackoverflow.com/questions/51327982/using-bound-google-scripts-to-generate-a-query-object/51328419#51328419

[fix falcon]

Greetings, thanks for your support, running the Script gives me this error:

TypeError: SpreadsheetApp.getSpreadsheetById is not a function

 

I know how to put the Id of the Spreadsheet, I have been doing it well in other scripts, I do it like this:

I take the Spreadsheet Master Id

https://docs.google.com/spreadsheets/d/----------------------This part is what I take as Id------------------------------/edit#gid=0

Then I put it like this:

let master = SpreadsheetApp.getSpreadsheetById([----------------------This part is what I take as Id------------------------------ ]).getSheetByName("Unid1T1!B2");

Result: won't let me save

But if I put the quotes to the Id:

let master = SpreadsheetApp.getSpreadsheetById(["----------------------This part is what I take as Id------------------------------" ]).getSheetByName("Unid1T1!B2");

I execute, and the result is:

TypeError: SpreadsheetApp.getSpreadsheetById is not a function

What is going wrong?

[Andrew Roberts]

It looks like you are putting the ID in square brackets - i.e. passing an array rather than a string.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9c94d849-1c23-4831-b2df-6b9c6483365fo%40googlegroups.com.

[fix falcon]

Greetings, thanks for your guidance. Let's see again:

I have two files:

1.- MasterControl

2. Review

In the review file I have a button that I want to execute when it brings me the data from the MasterControl file

I have this scrpit, which I have removed the brackets

function ImportPregunta() {

let master = SpreadsheetApp.getSpreadsheetById("-------This part is the ID of the MasterControl file-----").getSheetByName("Unid1T1!B2");  // MasterControl file

let masterCell = master.getRange("B2").getValue();   // MasterControl file

let receiver = SpreadsheetApp.getActiveSpreadsheet();

receiver.getRange("C2").setValue(masterCell); // Review file

};

but I get the error message.

TypeError: SpreadsheetApp.getSpreadsheetById is not a function

It does not work

What is the error?

[fix falcon]

Greetings, thanks for your guidance. Let's see again:

I have two files:

1.- MasterControl

2. Review

In the review file I have a button that I want to execute when it brings me the data from the MasterControl file

I have this scrpit, which I have removed the brackets

function ImportPregunta() {

let master = SpreadsheetApp.getSpreadsheetById("-------This part is the ID of the MasterControl file-----").getSheetByName("Unid1T1!B2");  // MasterControl file

let masterCell = master.getRange("B2").getValue();   // MasterControl file

let receiver = SpreadsheetApp.getActiveSpreadsheet();

receiver.getRange("C2").setValue(masterCell); // Review file

};

but I get the error message.

TypeError: SpreadsheetApp.getSpreadsheetById is not a function

[fix falcon]

I want to include an image to strengthen something

[Michael O'Shaughnessy]

I may be missing something but the error you are getting is correct.  There is no function "getSpreadsheetById" for "SpreadsheetApp".  I would suggest you try the "openById()" method.

So you should have

let master = SpreadsheetApp.openById("1233ABCD........");

On Sun, Jun 7, 2020 at 3:36 PM fix falcon <docenci...@gmail.com> wrote:

I want to include an image to strengthen something

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5375cdb5-d9df-4b17-b9e8-0d00ffdb1c81o%40googlegroups.com.

[fix falcon]

Greetings, thanks for the guidance,

I just tried, let master = SpreadsheetApp.openById ("1233ABCD ........");

but it doesn't work either, I get this message:

Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets

Any other suggestions???

[fix falcon]

By the way I can't say that I have even enabled, share as reading for anyone and even published it on the Web (file / post web)

[Kim Nilsson]

Hi, fix falcon,

it doesn't seem that you are using the wrapper I gave you?

I know that works to do real queries on data in a sheet, so it could be worth your time.

Do note that it has two parts, which you must use exactly how they are shown in the post.

You only need to insert the necessary values and you will get the results in the variable result.

[fix falcon]

Thank you,...

Well, it's true, I can't really find

Dimu Designs ("Here he is called Dimu Designs")

https://stackoverflow.com/questions/51327982/using-bound-google-scripts-to-generate-a-query-object/51328419#51328419

Not something similar to what I need ... maybe it is, but I can't understand I don't see anything from the script for my button

Two parts

The second part is more understandable, however you have those arguments that I do not see where to get

var result = Utils.gvizQuery
"<YOUR_SPREADSHEET_ID>",
 "<YOUR_QUERY_STRING>",???
<SHEET_ID_IF_NEEDED>, // ??? can be a number (the sheetId), or the name of the sheet; if not needed, but headers are, pass in undefined
<RANGE>, // specify range, ex: `A2:O`
<HEADER_ROW_INDEX_IF_NEEDED> // ??? always a numbe
);

The first worst part is not understandable
I do not know what to do....

[Kim Nilsson]

You need to add both parts as two separate gs files. 

Do absolutely nothing with the large code. It is like a "library" of code, only referred to by the small code. 

Add your own values into the the small code. That is where you put your real Query question with file id and range for query.

The result of your query will be set in the variable result, as you can see at the end of the small code.

Now, in your own code just run the function of the small code and you will get your answer in the variable result. 

[Kim Nilsson]

Sorry, in the simple/small code there is no function as it is a template only, you need to put it in one.

Like I did here. 

https://github.com/NoSubstitute/CB_LookUp/blob/master/queryCBS.gs

[fix falcon]

Greetings, I want to be honest. I don't understand anything, .. the small code has been put ID in a different way but nothing all comes out an error, .. I'm just guessing, trial - error but it doesn't work. Sorry to disappoint.

I haven't touched the big code and put it in the script editor of the master file. Small code in receiver file and rehearsed by setting leaf id and range differently but it doesn't work. Sorry

function queryCBS(sernum) {
	var sernoin = sernum;
	if (!sernoin.trim()) {{var sernoin = "-------This part, I put the ID of the MasterControl file-----"};}
	else {{var serno = sernoin}
	var qUser = "Unid1T1!B2" + serno + "'";//You can select any three columns you want to be in the result
	var result = Utils.gvizQuery(
	"-------This part I put the ID of the Receiver file-----",
	qUser,
	"Devices",
	"C2");//If you change the query above, you may also need to change this range. Remember to edit the column headers, if you do.
	}
	Logger.log([serno, result]); // Logging both incoming serial and result to console. Can be disabled.
	return [result];
	}

[fix falcon]

Greetings, I want to be honest. I don't understand anything, .. the small code has been put ID in a different way but nothing all comes out an error, .. I'm just guessing, trial - error but it doesn't work. Sorry to disappoint.

I haven't touched the big code and put it in the script editor of the master file. Small code in receiver file and rehearsed by setting leaf id and range differently but it doesn't work. Sorry

function queryCBS(sernum) {
	var sernoin = sernum;
	if (!sernoin.trim()) {{var sernoin = "-------This part, I put the ID of the MasterControl file-----"};}
	else {{var serno = sernoin}
	var qUser = "Unid1T1!B2" + serno + "'";//You can select any three columns you want to be in the result
	var result = Utils.gvizQuery(
	"-------This part I put the ID of the Receiver file-----",
	qUser,
	"Devices",
	"C2");//If you change the query above, you may also need to change this range. Remember to edit the column headers, if you do.
	}
	Logger.log([serno, result]); // Logging both incoming serial and result to console. Can be disabled.
	return [result];
	}

El lunes, 8 de junio de 2020, 20:02:40 (UTC-5), Kim Nilsson escribió:

[Kim Nilsson]

Ok, you are definitely using it wrong.

I'll try to explain each line of code below.

sernum is the incoming value from your own code. The value you want to use in your query.

function queryCBS(sernum) {
	var sernoin = sernum; //This is just changing the incoming variable 'sernum' to be called 'sernoin' (serial number incoming)
	if (!sernoin.trim()) {{var sernoin = "No serial"};} //If there is no incoming value, set sernoin to be "No serial"
	else {{var serno = sernoin} //This is just again a new rename of the incoming variable 'sernoin' to 'serno'
	var qUser = "select B,A,D where B contains '" + serno + "'";//THIS IS THE QUERY!!! YOU HAVE  TO HAVE A REAL QUERY HERE!!!
	var result = Utils.gvizQuery(
	"Replace with fileID of your CB_Activity spreadsheet", //THIS IS THE FILE ID OF THE SOURCE DATA!
	qUser,//This is the query from above as a variable, Do not change
	"Devices",//This is the name of the Sheet
	"A2:D");//This is the range of the source data, needs to contain all values in the query

	}
	Logger.log([serno, result]); // Logging both incoming serial and result to console. Can be disabled.
	return [result];
	}

But, as I write this, I read back to your original question, and I think my suggestion of using this query snippet is not at all what you want to do! :-)

I definitely didn't read your original question closely enough to understand what you are really trying to do.

You are really only trying to grab a single value from the cell Unid1T1!B2 in Master sheet and put it inside cell C2 of Receiver sheet.

Wow, I'm so sorry. I made this much more complicated that it should have been!

mro.michael's suggestion is much better for you.

I'll try to do and example of what he suggested, and see if I can figure it out for you.

[fix falcon]

Thank you

  I better share, the link of the source and destination files to make it look better

source file

https://docs.google.com/spreadsheets/d/1V9OEFf_Hi1eVlHUfgTdQipNIwUMC4XJESD6oXZLzP5M/edit?usp=sharing

destination  file

https://docs.google.com/spreadsheets/d/18u5rGAFlYZbAdrHAAOm4wJRbJwz0CUaCUSMgc0s7-6Q/edit?usp=sharing

[fix falcon]

Greetings, The file is already in free access, I did not realize

[fix falcon]

Greetings, it is not possible to achieve it, is it? Bring data from another Spreadsheet with a button ...

El viernes, 12 de junio de 2020, 1:58:25 (UTC-5), Kim Nilsson escribió:

[Kim Nilsson]

Hi, fix!

I have a long draft of an answer to this on my home computer. :-)

You have made a few mistakes I have noted.

First one being that you must have all scripts in the destination sheet. Not in the Master sheet.

I will try to complete the email with the rest of the things that are wrong from home.

Kim

[fix falcon]

Yes, so I have the two files, the one executed by the user (students) is the one with the buttons

 

[Kim Nilsson]

Yes, it should have all scripts.

No scripts in Master. 

I have moved the script to the destination. 

But, there are more incorrect code, so it still doesn't work as it should. 

[fix falcon]

Greetings, this problem may not be solved. Will there be no script for this ...