Skip to content
Advertisement

Authorization error still persists when calling getDataSourceFormula() even after allowing script to acces Google Sheets

I am trying to get the content of a cell’s formula from a custom function that I wrote. I’m just trying to get the formula stored in B2. In Google Sheets. I am getting “Exception: Authorization is required to perform that action. (line 5).“. I tried revoking the authorization, adding the next first three lines and authorizing again. Nothing worked.

/**
 * @OnlyCurrentDoc
 */

function test() {
  return SpreadsheetApp.getActiveSheet().getRange('B2').getDataSourceFormula().getFormula();
}

I can confirm that the error is coming from getDataSourceFormula(). I tried replacing it with other functions, and they worked normally. What is the problem?

Thanks.

Advertisement

Answer

You are using incorrectly getDataSourceFormula() as you are combining it with getFormula().

getFormula() returns the top left cell formula of a range while getDataSourceFormula() returns the DataSourceFormula for the first cell in the range. Thus, getDataSourceFormula will only work for data sources, here is more official information regarding what these are.

As you didn’t mention anything about data sources in your question I am assuming that you are not using them and therefore you code line should look like this:

  return SpreadsheetApp.getActiveSheet().getRange('B2').getFormula();
Advertisement