Search code examples
functiongoogle-apps-scriptgoogle-sheets

GAS custom function error pulling data from another spreadsheet

I'm attempting to pull data into a custom function in spreadsheet1 from a different spreadsheet and get the following error:

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

Looked this up and added the following:

  1. *@NotOnlyCurrentDoc - In the function description area
  2. Added "oauthScopes": ["https://www.googleapis.com/auth/spreadsheets.readonly"] into the appscript.json file
  3. Enabled the Googles Sheets API, per documentation advising to the this

Still no luck. Even running the function from a custom menu and explicitly providing the required permission and the error persists.

The documentation states that the spreadsheet service:

Spreadsheet: Read only (can use most get*() methods, but not set*()). Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()).

Is there a work around for this issue, apart from shoving all the data into a single spreadsheet? While this is a personal project, I can see the same issue rearing itself with workplace spreadsheets and the "all in one spreadsheet" NOT being a viable solution. Any help would be great!

Custom function to be called:

    /**
*@customfunction
*@NotOnlyCurrentDoc
*/
function altWrktsTblSrch5(paramWrkout)
{
  //SpreadsheetApp.setActiveSheet & opendByUrl do NOT open the spreadsheet when it is a DIFFERENT spreadsheet being called BUT these 
  var dbSpreadSheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1l6_NGs3YFKPyBuNA-5xVzlJRL8aB0zFwb-EOunyVzqU/edit?pli=1#gid=45618254');
  
  //var sheet_alt_wrkts = dbSpreadSheet.getSheetByName("Alt Workouts TABLE").getRange(2,1,100,6);
  var range_alt_wrkouts = sheet_alt_wrkts.getValues();
  var avlbl_non_sqntnl_alt_workts_array = range_alt_wrkouts.filter(e => e[3] && e[3] && e[4] == 'Yes' && e[5] == 'Non-sequential'); 
  var cols = [1, 3];
  var alt_wrkouts_array = avlbl_non_sqntnl_alt_workts_array.map(r => cols.map(i => r[i-1]));
  console.log(alt_wrkouts_array);
  var argWrkout = paramWrkout;

  //"Virtually" flattens the array without altering it and finds the ABSOLUTE index position of the desired element
  var index = [].concat.apply([], ([].concat.apply([], alt_wrkouts_array))).indexOf(argWrkout); //'Walking' is passed in by the code in the function at the top
  console.log("Index: " +index );
  var numCols = alt_wrkouts_array[0].length;
  console.log("Number of columns: " +numCols);
  var rows = parseInt(index / numCols);
  console.log("Number of rows: " +rows);
  var cols = index % numCols;
  console.log(cols);
 

  //Test to confirm whether at the last row of the TYPE of alternative activity if NOT normal actions 
  if (alt_wrkouts_array[rows][cols-1] != alt_wrkouts_array[rows+1][cols-1])
  {
    index2 = [].concat.apply([], ([].concat.apply([], alt_wrkouts_array))).indexOf(alt_wrkouts_array[rows][cols-1]);
    console.log("Index2: " +index2);
    numCols2 = alt_wrkouts_array[0].length;
    console.log("Number of columns2: " +numCols2);
    rows2 = parseInt(index2 / numCols2);
    console.log("Number of rows2: " +rows2);
    cols2 = index2 % numCols2;
    console.log(cols2);
    return console.log(alt_wrkouts_array[rows2][cols2+1]); //returns to the first workout of the current alternate workout
  }
}
Solution

  • Unfortunately, in the current stage, SpreadsheetApp.openByUrl cannot be used with the custom function, because of the current specification. But, about Is there a work around for this issue, apart from shoving all the data into a single spreadsheet?, in this case, as a workaround, how about using the following flow?

    1. Publicly share your Spreadsheet (https://docs.google.com/spreadsheets/d/1l6_NGs3YFKPyBuNA-5xVzlJRL8aB0zFwb-EOunyVzqU/edit) for retrieving the values.
    2. Retrieve the values from the Spreadsheet using UrlFetchApp as CSV data.
      • UrlFetchApp can be used with the custom function.
    3. Parse the retrieved values as a 2-dimensional array of range_alt_wrkouts.

    By this flow, the values can be retrieved from https://docs.google.com/spreadsheets/d/1l6_NGs3YFKPyBuNA-5xVzlJRL8aB0zFwb-EOunyVzqU/edit with the custom function.

    When this is reflected in your script, how about the following modification?

    Modified script:

    Please confirm your Spreadsheet URL and ID again. Because your URL of var dbSpreadSheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1l6_NGs3YFKPyBuNA-5xVzlJRL8aB0zFwb-EOunyVzqU/edit?pli=1#gid=45618254'); in your showing script is different from your provided Spreadsheet. Please be careful about this.

    From:

    var dbSpreadSheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1l6_NGs3YFKPyBuNA-5xVzlJRL8aB0zFwb-EOunyVzqU/edit?pli=1#gid=45618254');
var sheet_alt_wrkts = dbSpreadSheet.getSheetByName("Alt Workouts TABLE").getRange(2, 1, 100, 6);
var range_alt_wrkouts = sheet_alt_wrkts.getValues();

    To:

    var id = "1l6_NGs3YFKPyBuNA-5xVzlJRL8aB0zFwb-EOunyVzqU";
var url = `https://docs.google.com/spreadsheets/export?exportFormat=csv&id=${id}&range=${encodeURIComponent("A2:F101")}`;
var v = UrlFetchApp.fetch(url).getContentText();
var range_alt_wrkouts = Utilities.parseCsv(v);
    • By modifying with the above workaround, when =altWrktsTblSrch5("Walking") is put into a cell using your provided Spreadsheets, Mushin Meditation is returned.

    Note:

    • In this case, your Spreadsheet of "Test - Alt Workouts Spreadsheet" is required to be publicly shared. When the Spreadsheet is not publicly shared, this modified script cannot be used. Please be careful about this.

    • When your showing script is modified with the above workaround, it becomes as follows. In your script, return console.log(alt_wrkouts_array[rows2][cols2+1]); is used. In this case, no value is returned. Please be careful about this. In this modification, I modified it to return alt_wrkouts_array[rows2][cols2+1];.

    /**
*@customfunction
*@NotOnlyCurrentDoc
*/
function altWrktsTblSrch5(paramWrkout) {
  var id = "1l6_NGs3YFKPyBuNA-5xVzlJRL8aB0zFwb-EOunyVzqU";
  var url = `https://docs.google.com/spreadsheets/export?exportFormat=csv&id=${id}&range=${encodeURIComponent("A2:F101")}`;
  var v = UrlFetchApp.fetch(url).getContentText();
  var range_alt_wrkouts = Utilities.parseCsv(v);

  var avlbl_non_sqntnl_alt_workts_array = range_alt_wrkouts.filter(e => e[3] && e[3] && e[4] == 'Yes' && e[5] == 'Non-sequential');
  var cols = [1, 3];
  var alt_wrkouts_array = avlbl_non_sqntnl_alt_workts_array.map(r => cols.map(i => r[i - 1]));
  console.log(alt_wrkouts_array);
  var argWrkout = paramWrkout;

  var index = [].concat.apply([], ([].concat.apply([], alt_wrkouts_array))).indexOf(argWrkout);
  console.log("Index: " + index);
  var numCols = alt_wrkouts_array[0].length;
  console.log("Number of columns: " + numCols);
  var rows = parseInt(index / numCols);
  console.log("Number of rows: " + rows);
  var cols = index % numCols;
  console.log(cols);

  if (alt_wrkouts_array[rows][cols - 1] != alt_wrkouts_array[rows + 1][cols - 1]) {
    index2 = [].concat.apply([], ([].concat.apply([], alt_wrkouts_array))).indexOf(alt_wrkouts_array[rows][cols - 1]);
    console.log("Index2: " + index2);
    numCols2 = alt_wrkouts_array[0].length;
    console.log("Number of columns2: " + numCols2);
    rows2 = parseInt(index2 / numCols2);
    console.log("Number of rows2: " + rows2);
    cols2 = index2 % numCols2;
    console.log(cols2);

    console.log(alt_wrkouts_array[rows2][cols2 + 1]);
    return alt_wrkouts_array[rows2][cols2 + 1];
  }
  return null;
}

    • If you cannot publicly shared your Spreadsheet, as the 2nd woaround, how about retrieving the values using IMPORTRANGE? When this is reflected in your script, it becomes as follows. In this case, please put a function of =altWrktsTblSrch5(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1l6_NGs3YFKPyBuNA-5xVzlJRL8aB0zFwb-EOunyVzqU/edit","'Alt Workouts TABLE'!A2:F101"),"Walking") into a cell. By this, the values are retrieved without publicly shared the Spreadsheet. In this case, before you put the function, please put =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1l6_NGs3YFKPyBuNA-5xVzlJRL8aB0zFwb-EOunyVzqU/edit","'Alt Workouts TABLE'!A2:F101") into a cell and authorize it. By this, the above custom function can be used.

    /**
*@customfunction
*@NotOnlyCurrentDoc
*/
function altWrktsTblSrch5(range_alt_wrkouts, paramWrkout) {
  var avlbl_non_sqntnl_alt_workts_array = range_alt_wrkouts.filter(e => e[3] && e[3] && e[4] == 'Yes' && e[5] == 'Non-sequential');
  var cols = [1, 3];
  var alt_wrkouts_array = avlbl_non_sqntnl_alt_workts_array.map(r => cols.map(i => r[i - 1]));
  console.log(alt_wrkouts_array);
  var argWrkout = paramWrkout;

  var index = [].concat.apply([], ([].concat.apply([], alt_wrkouts_array))).indexOf(argWrkout);
  console.log("Index: " + index);
  var numCols = alt_wrkouts_array[0].length;
  console.log("Number of columns: " + numCols);
  var rows = parseInt(index / numCols);
  console.log("Number of rows: " + rows);
  var cols = index % numCols;
  console.log(cols);

  if (alt_wrkouts_array[rows][cols - 1] != alt_wrkouts_array[rows + 1][cols - 1]) {
    index2 = [].concat.apply([], ([].concat.apply([], alt_wrkouts_array))).indexOf(alt_wrkouts_array[rows][cols - 1]);
    console.log("Index2: " + index2);
    numCols2 = alt_wrkouts_array[0].length;
    console.log("Number of columns2: " + numCols2);
    rows2 = parseInt(index2 / numCols2);
    console.log("Number of rows2: " + rows2);
    cols2 = index2 % numCols2;
    console.log(cols2);

    console.log(alt_wrkouts_array[rows2][cols2 + 1]);
    return alt_wrkouts_array[rows2][cols2 + 1];
  }
  return null;
}

    • By the way, in your question, you say Added "oauthScopes": ["https://www.googleapis.com/auth/spreadsheets.readonly"] into the appscript.json file. In this case, when you directly run the function, https://www.googleapis.com/auth/script.external_request is also required to be used. So, please be careful about this.