1

I have this piece of code that is working perfectly when I use it in a standalone Google Script running as a web-app :

function getSubjectsRollNosFromGradeSheet() {
  var spreadsheetId = '171hWWH8bV6kM1JJ2dvL80q3BoWC6eEc0U7l7wCTaXHE';
  var rangeSubject = 'GradesT1!G1:AA1';

  var values = Sheets.Spreadsheets.Values.get(spreadsheetId, rangeSubject).values;

However when I write this same code in a Script that is embedded inside another Google sheet (not the one being accessed for data) attached to a Google Form I get an error :

function updateForm(){

  // call your form and connect to the drop-down item
  var form = FormApp.openById("1VV_LeFJODZcIzfcYOh8vHEkhl033Lx4ZsXaY4Pm1ZUE");
  var subList1 = form.getItemById("497738674").asListItem();

  //pull data from another sheet
  var spreadSheetID = "1EJSx62rZuLSKIgel7LH8hE7-68lupz4buutmVQBqi2I";
  var rangeSubs = 'Sheet1!A1:A';

  var subValues = Sheets.Spreadsheets.Values.get(spreadSheetID, rangeSubs).values;

I get this error

ReferenceError: "Sheets" is not defined. (line 16, file "Code")

Why is the Sheets object available in the first case but not in the second case and how should I change my code to overcome this error.

1 Answer 1

2

Sheets is one of methods of Advanced Google Services. So in order to use Sheets of Advanced Google Services, please enable Sheets API for Advanced Google Services and API console as follows.

Please do this at the bound script which included the script of your question.

Enable Sheets API v4 at Advanced Google Services

  • On script editor
    • Resources -> Advanced Google Services
    • Turn on Google Sheets API v4
    • Enable Sheets API v4 at API console

Enable Sheets API v4 at API console

  • On script editor
    • Resources -> Cloud Platform project
    • View API console
    • At Getting started, click Enable APIs and get credentials like keys.
    • At left side, click Library.
    • At Search for APIs & services, input "sheets". And click Google Sheets API.
    • Click Enable button.

References :

If I misunderstand your question, I'm sorry.

Sign up to request clarification or add additional context in comments.

2 Comments

Yes. This solves the problem. Stupid of me to have missed this.
@Calcutta I'm glad I could help. At recent update of Google, Manifests was added to Google Apps Script. By using this, Advanced Google Services can be also enabled by json. The detail information is here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.