Google apps script not identifying sheet correctly

Question

I'm running a templated google script that constructs an html page with the contents of a google spreadsheet.

When I run the script with the spreadsheet https://docs.google.com/spreadsheet/ccc?key=0AtDy2rbIwVyldE82MG50SkR3UWYyam5YRVd1ajZlcnc&usp=drive_web#gid=0 everything works fine.

When I run the script with the spreadsheet https://docs.google.com/spreadsheets/d/1A0Kd_BKeO9IB-SJvRMhC68q0XMQLrXarVVPlFJa_fMg/edit#gid=0 I get the message that I need to first select an active spreadsheet.

Here is the script:

function doGet() {
  return HtmlService
      .createTemplateFromFile('index')
      .evaluate();
}

function getData() {
  var mysheet = SpreadsheetApp.openById('SPREADSHEET_KEY').getDataRange().getValues();
  return mysheet;
}

Here is the index.html file:

<? var data = getData(); ?>
<table>
  <? for (var i = 0; i < data.length; i++) { ?>
    <tr>
      <? for (var j = 0; j < data[i].length; j++) { ?>
        <td><?= data[i][j] ?></td>
      <? } ?>
    </tr>
  <? } ?>
</table>

It seems that the problem lies in the second spreadsheet.

Any help will be apreciated.

Answer 1

The reference you are currently assigning to mysheet refers to the document as a whole. You still need to grab a reference to the sheet that contains the data.

function getData() {
  var spreadsheet = SpreadsheetApp.openById('SPREADSHEET_KEY').getDataRange().getValues();
  var mysheet = spreadsheet.getSheetByName('NameOfTheSheet');
  return mysheet;
}

Answer 2

use .getActiveSheet() and the script would be:

function doGet() {
  return HtmlService
      .createTemplateFromFile('index')
      .evaluate();
}

function getData() {
  var mysheet = SpreadsheetApp.openById('SPREADSHEET_KEY')**.getActiveSheet()**.getDataRange().getValues();
  return mysheet;
}