Is there a way to call "Sheets.Spreadsheets.Values.get()" with an apps script scope of spreadsheets.currentonly?

Question

From Apps Script I am trying to get all the values from a specific sheet in the current active spreadsheet - importantly using the RenderOption parameters (valueRenderOption, dateTimeRenderOption) available to Sheets.Spreadsheets.Values.get().

Since the script is only pulling data from the current active sheet, I want to use a scope of .../spreadsheets.currentonly limiting access as much as possible. But, when doing so, I get this error: "API call to sheets.spreadsheets.values.get failed with error: Request had insufficient authentication scopes."

However, if I remove 'currentonly' from the scope, the call works.

Apps Script code snippet:

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sid = ss.getId();
    var range = ss.getDataRange();
    var rn = 'Sheet1!' + range.getA1Notation();
    var parms = {valueRenderOption: 'UNFORMATTED_VALUE', dateTimeRenderOption: 'SERIAL_NUMBER'};
    var sheet1in = Sheets.Spreadsheets.Values.get(sid, rn, parms);

when the appsscript.json oauth scope includes:
"https://www.googleapis.com/auth/spreadsheets.currentonly" the script fails. Removing 'currentonly' from the scope resolves the problem but requires the user to Allow: "See, edit, create, and delete all your Google Sheets spreadsheets"
even though the script only accesses: "...spreadsheets that this application has been installed in"

Coding:

    /**
      * @OnlyCurrentDoc
      */

doesn't solve the problem. It changes the default scope to "See, edit, create, and delete all of your Google Drive files"
which is no better

Thanks in advance

Answer 1

Yes. If you have Sheets API enabled.

I'm not sure what you mean about the format of the return from a get. If you parse it into an object and look at the values property you will see that it looks something like this:

[["COL1","COL2","COL3","COL4","COL5","COL6","COL7","COL8","COL9","COL10"],["1","2","3","4","5","6","7","8","9","10"],["2","3","4","5","6","7","8","9","10","11"],["3","4","5","6","7","8","9","10","11","12"],["4","5","6","7","8","9","10","11","12","13"],["5","6","7","8","9","10","11","12","13","14"],["6","7","8","9","10","11","12","13","14","15"],["7","8","9","10","11","12","13","14","15","16"],["8","9","10","11","12","13","14","15","16","17"],["9","10","11","12","13","14","15","16","17","18"]]

It looks like a 2D array. Which is exactly what setValues() requires

Here's the code that generated the data:

function temp() {
  const sssId = "1Trccc5IszA2cLpUHKr2_I826e216u6is7ZWbD8ecx2U";
  const sss = SpreadsheetApp.openById(sssId);
  const dss = SpreadsheetApp.getActive();
  const dssId = dss.getId();
  const ssh = sss.getSheetByName('Sheet1');//Source sheet
  const srg = ssh.getRange(1, 1, ssh.getLastRow(), ssh.getLastColumn());
  const dsh = dss.getSheetByName("Sheet1");//Destination sheet
  =>var vs = Sheets.Spreadsheets.Values.get(sssId, `${ssh.getName()}!${srg.getA1Notation()}`).values;
  console.log(JSON.stringify(vs));
  const lr = dsh.getLastRow();
  if(lr == dsh.getMaxRows()) {
    dsh.insertRowsAfter(lr,vs.length);
  }
  const drg = dsh.getRange(lr + 1, 1, vs.length, vs[0].length);//appends to bottom of spreadsheet
  Sheets.Spreadsheets.Values.update({ values: vs }, dssId, `${dsh.getName()}!${drg.getA1Notation()}`, { valueInputOption: "USER_ENTERED" });
}