Exception: You do not have permission to call setValues

Question

I have 2 tabs in my Google Sheet:

dashboard:

db:

On dashboard in column B I call function yahoofinance(). This function checks if for the given ticker there exists data in db. If so, this data is returned. If not, OR if so but the data is empty, Yahoo! Finance is contacted to retrieve the data. So far so good.

Take JPM as an example now. It is called in row 3 in dashboard. In db we do find JPM but there is no data for the ticker, so we retrieve it live from Yahoo! Finance. Subsequently, I want to update the JPM row in db with this data, so that next time we open the dashboard, we do not contact Yahoo! again for this information.

However, see line under // update existing row.. the code generates an error Exception: You do not have permission to call setValues and I do not know how to solve it. Do you? Any help is greatly appreciated!

function yahoofinance(ticker) {
  // First check if we have this data stored already
  var db      =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('db'); 
  var tickers = db.getRange('A2:A').getValues();
  var stored  = false;
  var row     = 2;

  for (var r = 0; r <= tickers.length; r++) { 
    if (tickers[r] == ticker) { stored = true; row = row + r; }
  }

  if (stored == true) { // the ticker is known in db
    var range = db.getRange(row, 2, 1, 4);

    if (range.isBlank()) { // ticker is known but no data yet
      var data = get_live_data(ticker);

      // update existing row
      db.getRange(row, 2).setValues(data); 

      // return data
      return data;

    }
    else { 
      return range.getValues(); 
    }
  }
  else {
    var data = get_live_data(ticker);

    // append row to db


    // return data to sheet
    return data;
  }
}

function get_live_data(ticker) {
  const url = 'https://query2.finance.yahoo.com/v10/finance/quoteSummary/' + encodeURI(ticker) + '?modules=price,assetProfile,summaryDetail';
  
  let response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  if (response.getResponseCode() == 200) {
      var object = JSON.parse(response.getContentText());
  }

  let fwdPE  = object.quoteSummary.result[0]?.summaryDetail?.forwardPE?.fmt || '-';
  let sector = object.quoteSummary.result[0]?.assetProfile?.sector || '-';
  let mktCap = object.quoteSummary.result[0]?.price?.marketCap?.fmt || '-';

  return [[fwdPE, sector, mktCap]];
}

score 1 · Answer 1 · answered May 30 '22 at 18:59

So based on line under // update existing row, you want to add the data in the other sheet and based on documentation, custom functions return values but will only set values in the current cell where you're using the function, you can't modify data in other cell and that's why you're getting the error. You're calling the function in B4 and are trying to set values in other sheet at the same time which is not allowed.

A custom function cannot affect cells other than those it returns a value to. In other words, a custom function cannot edit arbitrary cells, only the cells it is called from and their adjacent cells.

Exception: You do not have permission to call setValues

1 Answers1