0

After creating a calculator with a google sheet I need to share a forced copy of it (using the "/copy" parameter at the end of the sheet URL).

In the sheet I have inserted many formulas using the IMPORTRANGE function such as the following:

IMPORTRANGE("1AEfVRA0nZG8H3o4PnKUMa69yYXumbECo8wk6C4Cr53Y";"data!D14:G18") At the moment the user creates a forced copy of the sheet he gets a new ID for the sheet which I can make available in a cell using the custom function: =URL("key")

To make the calculator work correctly, I need that in every formula where the ID "1AEfVRA0nZG8H3o4PnKUMa69yYXumbECo8wk6C4Cr53Y" is replaced with the new ID obtainable through the formula =URL("key") as follow example:

ORIGINAL FORMULA:

IMPORTRANGE("**1AEfVRA0nZG8H3o4PnKUMa69yYXumbECo8wk6C4Cr53Y**";"data!D14:G18") NEW FORMULA:

IMPORTRANGE("**2AEcVRA0nZE8H3o5PnKUMa70yYXalcECo8wk7A4Cr12Z**";"data!D14:G18")

I tried to create a Macro but in the "replace with" field, if I insert the formula = =URL("key"), the result is that in all formulas I get an error.

I tried to understand if it is possible to create with the ADDRESS and CONCATENATE function the substitution of the ID dynamically in the formula but the result is always exposed as text therefore the formula is not executed but only shown.

At this point I realized that probably the only way to get the result is through a script but I couldn't figure out how.

I can't find solutions and after searching and reading hundreds of posts I decided to ask directly for a possible solution for the specific case.

Is there anyone who could help me?

Is it there a script to get this ID change in all formulas of all sheets by replacing it with the contents of the cell where the ID of the new sheet is made visible (using the formula =URL("key") ) created with the forced copy?

Thank you so much for your help

Matteo Maurizio
  • 1
  • Welcome to [so]. When the question is related an error, always include the textual error message. In the the case of using the Google Sheets Macro recorder, add the resulting code. Ref. [mcve]. To learn how to get the Macro code, see [Google Sheets Macros](https://developers.google.com/apps-script/guides/sheets/macros). To start learning about "scripts" in Google Sheets, please start by reading https://developers.google.com/apps-script/guides/sheets. – Rubén Oct 28 '22 at 10:46

1 Answers1

0

Instead of "hardcoding" the Google Sheets key on multiple formulas, put the it in a cell and replace the key in the formula by the cell reference. This way the user only have to replace the key in a single place.

Let say that you add the key to A1

A
1 1AEfVRA0nZG8H3o4PnKUMa69yYXumbECo8wk6C4Cr53Y

Instead of

IMPORTRANGE("**1AEfVRA0nZG8H3o4PnKUMa69yYXumbECo8wk6C4Cr53Y**";"data!D14:G18")

use

IMPORTRANGE(A1;"data!D14:G18")

If you still will use Google Apps Script, you might find the follwoing post about how to automatically authorize the access to the spreadsheet used in IMPORTRANGE --> How to allow access for importrange function via apps script?

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Thanks Ruben. a very effective solution. The only thing is that the script to obtain the ID of the new user's sheet involves the request for authorization to use it and the notification of risk as i'm an uncertified developer. I found the QUERY function as a possible alternative which avoids asking the user to import and authorize the script to retrieve the ID of the new sheet. The result, being the data in the same workbook, is similar to IMPORTRANGE although, unfortunately, it does not allow you to show the TEXT contents as they are in the range; Thanks anyway for the tip! – Matteo Maurizio Oct 28 '22 at 17:23
  • @MatteoMaurizio I added a Link to my answer – Rubén Oct 28 '22 at 19:16