在项目中使用谷歌应用程序脚本功能

电子表格绑定脚本在服务器端运行，您使用的SpreadsheetApp.getActiveSheet()方法只能在电子表格绑定的脚本上下文中工作，因为这是脚本实际"看到"活动电子表格的唯一情况。当您将其部署为Web应用程序时，您必须告诉脚本它必须查看哪个电子表格，例如使用SpreadsheetApp.openById('spreadsheet ID')方法。

但即使这样做也不允许在谷歌环境之外使用这样的代码（例如JS fiddle），因为SpreadsheetApp是谷歌应用程序服务特有的。

你必须记住，谷歌应用程序脚本是基于JavaScript的，但不是"普通"JavaScript，它使用了许多仅与谷歌应用程序相关的特定服务。

编辑回答您的以下评论：

电子表格中用作数据服务器的代码如下：（这是作为一个没有用户界面的网络应用程序部署的。它作为一个服务运行

function doGet(e) {
  if(e.parameter.mode==null){return ContentService.createTextOutput("error, wrong request").setMimeType(ContentService.MimeType.TEXT)};
  var mode = e.parameter.mode;
  var value = e.parameter.value;
  var ss = SpreadsheetApp.openById('1yad5sZZt-X6bIftpR--OSyf3VZWf3Jxx8UJBhh7Arwg');
  var sh = ss.getSheets()[0];
  if(mode=='read'){
    var sheetValues =  sh.getDataRange().getValues();// get data from sheet
    var valToReturn = ContentService.createTextOutput(JSON.stringify(sheetValues)).setMimeType(ContentService.MimeType.JSON);
    return valToReturn;// send it as JSon string
    }
  if(mode=='write'){
    var val = Utilities.base64Decode(value,Utilities.Charset.UTF_8);// decode base64 and get an array of numbers
    Logger.log(val);// see it !
    var stringVal = ''; // create an empty string
    for(var n in val){
      stringVal += String.fromCharCode(val[n]);// add each character in turn
    }
    var sheetValues =  JSON.parse(stringVal);// convert the string into an object (2D array)
    Logger.log(sheetValues);// check result
    sh.getRange(1,1,sheetValues.length,sheetValues[0].length).setValues(sheetValues);// update the sheet
    return ContentService.createTextOutput(JSON.stringify(sheetValues)).setMimeType(ContentService.MimeType.JSON);// send back the result as a string
    }
  return ContentService.createTextOutput('error').setMimeType(ContentService.MimeType.TEXT);// in case mode is not 'read' nor 'write'... should not happen ! 
}

您可以通过它的url+参数调用该服务，它将在电子表格中获取/设置值。这是一个基本的例子，但效果很好。

下面是在该电子表格中使用该服务的Ui的Web应用程序代码

var stylePanel = {'padding':'50px', 'background':'#FFA'};
var styleButton = {'padding':'5px', 'border-radius':'5px', 'borderWidth':'1px', 'borderColor':'#DDD','fontSize':'12pt'};
var styleTextItalic = {'fontSize':'12pt','fontStyle':'italic','fontFamily':'arial,sans-serif','color':'#F00'};
var styleTextNormal = {'fontSize':'12pt','fontStyle':'normal','fontFamily':'arial,sans-serif','color':'#00F'};
var styleLabel = {'fontSize':'12pt','color':'#F00'};
var url = 'https://script.google.com/macros/s/AKfycbwPioVjYMSrmhKnJOaF2GG83dnstLWI7isU9SF1vxPV8td-g9E7/exec';
var numRow = 21;// the number of rows in the grid = number of rows in the SS + 1
;
function doGet() {
  var app = UiApp.createApplication().setTitle('url_fetch_demo');
  var panel = app.createVerticalPanel().setStyleAttributes(stylePanel);
  var headers = ['Field Name','Your answer'];// grid title
  var grid = app.createGrid(numRow+2,2);// create the grid with right size
  var wait = app.createImage('https://dl.dropboxusercontent.com/u/211279/loading3T.gif').setId('wait').setVisible(false);// get a spinner image in animated gif
  var handlerWrite = app.createServerHandler('writeSheet').addCallbackElement(grid);// 2 handlers for the buttons
  var handlerRead = app.createServerHandler('readSheet').addCallbackElement(grid);
  var Chandler = app.createClientHandler().forTargets(wait).setVisible(true);// a client handler for the spinner
  var buttonWrite = app.createButton('Write to Sheet',handlerWrite).addClickHandler(Chandler).setStyleAttributes(styleButton);
  var buttonRead = app.createButton('Read from Sheet',handlerRead).addClickHandler(Chandler).setStyleAttributes(styleButton);
  for(var n=1 ; n < numRow ; n++){
    for(var m=0 ; m < 2 ; m++){ // create all the textBoxes with names & IDs
      var textBox = app.createTextBox().setText('no value').setName('text'+n+'-'+m).setId('text'+n+'-'+m).setStyleAttributes(styleTextNormal);
    //if(m==0){textBox.setEnabled(false)};// prevent writing to left column (optional)
      grid.setWidget(n,m,textBox);// place widgets
    }
  }
  grid.setWidget(numRow,0,buttonRead).setWidget(numRow,1,buttonWrite).setWidget(numRow+1,1,wait) // place buttons
  .setWidget(0,0,app.createLabel(headers[0]).setStyleAttributes(styleLabel)) // and headers
  .setWidget(0,1,app.createLabel(headers[1]).setStyleAttributes(styleLabel));
  app.add(panel.add(grid));
  return app; // show Ui
}
function writeSheet(e){
  var app = UiApp.getActiveApplication();
  app.getElementById('wait').setVisible(false);// spinner will be hidden when fct returns
  var dataArrayImage = [];// an array to get typed values
  for(var n=1 ; n < numRow ; n++){ 
    var row=[];
    for(var m=0 ; m < 2 ; m++){
      row.push(e.parameter['text'+n+'-'+m]); // get every value in every "cell"
      var textBox = app.getElementById('text'+n+'-'+m).setStyleAttributes(styleTextItalic);// update "cells" style
      //textBox.setText('written value = '+e.parameter['text'+n+'-'+m]);// rewrite to the cells - not usefull but serves to check while debugging
    }
    dataArrayImage.push(row);// store one row(=2cells)
  }
  var UiValues = JSON.stringify(dataArrayImage);// stringfy the array
  var newValues = url+'?mode=write&value='+Utilities.base64Encode(UiValues,Utilities.Charset.UTF_8);// add to url & parameters+ encode in pure ASCII characters
  Logger.log(newValues);// check in logger
  var check = UrlFetchApp.fetch(newValues).getContent();// get back the result
  Logger.log(check);// check result = newValues sent back in bytes format
  return app;//update Ui
}
function readSheet(e){
  var app = UiApp.getActiveApplication();
  app.getElementById('wait').setVisible(false);
  var returnedValue = UrlFetchApp.fetch(url+'?mode=read').getContentText();// get data from server
  Logger.log(returnedValue);// check values
  var sheetValues = JSON.parse(returnedValue);
  for(var n=1 ; n < numRow ; n++){
    for(var m=0 ; m < 2 ; m++){
      var textBox = app.getElementById('text'+n+'-'+m).setStyleAttributes(styleTextNormal);
      textBox.setText(sheetValues[n-1][m]);// iterate and update cells values
    }
  }
return app;// update Ui
}