Dynamically Updating Formulas From Another Google Spreadsheet

Question

I have two google spreadsheets, a master copy and a child copy. When I update the formulas in the master copy, I want it to update the formulas in the child copy too. I intend to create many copies of the child copy and don't want to update the formulas in each one every time something small has to change.

I've tried using IMPORTRANGE(), and it only gives me the values, not the formulas. So I thought to create my own script to get this done. I'm using the following code to attempt this:

function REMOTEDATA(inKey, inRange) {

  var outData;  
  var ss = SpreadsheetApp.openById(inKey);

  if (ss) {
     outData = ss.getRange(inRange).getFormulas();
  }

  return outData;
}

//My manifest is the following:
{
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets"
  ],
  "timeZone": "America/Denver",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER"
}

I'm very new to oAuth and I don't understand how to get this to work. When I attempt this it gives me the following:

"You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets (line 4)."

Can anyone tell me what I need to add to get this to work? If there's an easier way, I'm all ears.

EDIT: After reading Ruben's comment below, I changed the way I was doing it to having a button on the sheet itself run the function. It fixed the first problem, but now I'm running into another problem. I can't copy all the data directly from one sheet to another. I'm trying to get formulas, values, and formatting. If I just use getFormulas() and setFormulas() with the ranges, it gets rid of all the values that aren't formulas, and it doesn't change the formatting. I've been trying to use copyTo() but it tells me I can't do this between spreadsheets. My current code is:

function REMOTEDATA() {

  var MASTERKEY = "key";
  var UPDATERANGES = ["A1:B200", "C7:C200", "D1:H200", "J1:L200", "N1:S200", "U1:X200", "z1:ac200", "AE1:AH200", "AJ1:AL200"];

  var ss = SpreadsheetApp.openById(MASTERKEY);
  var cur_ss = SpreadsheetApp.getActiveSpreadsheet();

  UPDATERANGES.forEach(function(range) {

    data = ss.getRange(range).copyTo(cur_ss.getRange(range));


  });
}

My head is starting to hurt looking at the different methods for the Ranges Class. I can't see any methods to do this, but I feel like I'm missing something. Any other ideas?

EDIT 3: Okay. I figured out a workaround. Basically what I did was copy the entire sheet from one spreadsheet to the other using the sheet.copyTo() function. Then after that I used the range.copyTo() to get what I wanted. Then I deleted the copied sheet using sheet.deleteSheet()

Possible duplicate of Custom function constantly throwing an error — TheMaster
On this site solutions should be posted as answers not as questions edits. — Rubén

Rubén Rubén · Accepted Answer · 2019-06-01T20:58:22

Custom functions run anonymously so they can't call services that require authorization, like openById.

Instead of using a custom function to execute your script use another method like a custom menu, assign the script to an image, etc. or just run it from the script editor.

NOTE: You will have to modify your script a bit or use a another function to pass the required arguments.

Dynamically Updating Formulas From Another Google Spreadsheet

1 Answers