CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-speech-api
Lauren
Lauren

Reputation: 391

Google sheets script works as a script, but not a web app?

Sorry if I'm missing something really obvious here, Google scripts aren't my strong suit.

I'd like to be able to find data in a cell and overwrite it from an another site. I was attempting to modify this answer:

function test(){
 var sh = SpreadsheetApp.getActiveSheet();
 var data = sh.getDataRange().getValues(); // read all data in the sheet
 for(n=0;n<data.length;++n){ // iterate row by row and examine data in column A
 if(data[n][0].toString().match('xyz')=='xyz'){ data[n][5] = 'YES'};// if column A contains 'xyz' then set value in index [5] (is column F)
 }
 Logger.log(data)
 sh.getRange(1,1,data.length,data[0].length).setValues(data); // write back to the sheet
 }

by 1) changing SpreadsheetApp.getActiveSheet(); to SpreadsheetApp.openById("1ZHFGKtY7AaAtdMN8D5V9f2WTf8t9ldOW2pZ5my1CKnE").getSheetByName("Sheet1");, and 2) wrapping it in a doGet() function, so it looks like this:

function doGet(e) {  
  var sh = SpreadsheetApp.openById("1ZHFGKtY7AaAtdMN8D5V9f2WTf8t9ldOW2pZ5my1CKnE").getSheetByName("Sheet1");
  var data = sh.getDataRange().getValues(); // read all data in the sheet

  for(n=0;n<data.length;++n){ // iterate row by row and examine data in column A
    if(data[n][0].toString().match('xyz')=='xyz'){ 
      data[n][5] = 'YES'
    }; // if column A contains 'xyz' then set value in index [5] (is column F)
    sh.getRange(1,1,data.length,data[0].length).setValues(data); // write back to the sheet
  }
}

The function works fine as a script, but as soon as I add it to doGet() nothing happens, even though I get a message saying the script has completed. What is it I'm missing? :\

EDIT: It seems to be working form the "Test web app for your latest code" option; but even if I create a new version it still doesn't work from the link.

EDIT2: So although I'm not 100% sure what the problem was, it seems to have had something to do with being signed in to multiple Google accounts and setting "Who has access to this app". Or possibly an older version of the script got cached somewhere. Either way it's working now. 😕

Upvotes: 1

Views: 1407

Answers (4)

Lauren
Lauren

Reputation: 391

So although I'm not 100% sure what the problem was, it seems to have had something to do with being signed in to multiple Google accounts and setting "Who has access to this app". Or possibly an older version of the script got cached somewhere. Either way it's working now. 😕

Upvotes: 0

Cameron Roberts
Cameron Roberts

Reputation: 7367

Something strange is going on here, based on your video showing your deployment process it seems you are deploying correctly. The expected behaviour after following those steps that the published script link will work exactly as the dev link. I don't think this issue is anything you are doing wrong, but rather an odd Google issue.

The solution is creating a fresh copy of the script and publishing that, and trying it at the new URL.

I have seen similar problems in Apps Script in other scenarios, where it seems like a previous copy of the script is cached somewhere and the behaviour when executing doesn't match what is displayed in the script editor.

Upvotes: 2

Cooper
Cooper

Reputation: 64082

This is a simple example of a web app that puts an editable spreadsheet on an HTML Page. Publish as a webapp. You have to provide he SpreadsheetID and Sheet Name in the top two lines of following code.

Code.gs:

var SSID='SpreadsheetID';
var sheetName='Sheet Name';
function htmlSpreadsheet(mode)
{
  var mode=(typeof(mode)!='undefined')?mode:'dialog';
  var br='<br />';
  var s='';
  var hdrRows=1;
  var ss=SpreadsheetApp.openById(SSID);
  var sht=ss.getSheetByName(sheetName);
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  s+='<table>';
  for(var i=0;i<rngA.length;i++)
  {
    s+='<tr>';
    for(var j=0;j<rngA[i].length;j++)
    {
      if(i<hdrRows)
      {
        s+='<th id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="10" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
      } 
      else
      {
        s+='<td id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="10" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
      }
    }
    s+='</tr>';
  }
  s+='</table>';
  //s+='<div id="success"></div>';
  s+='</body></html>';
  switch (mode)
  {
    case 'dialog':
      var userInterface=HtmlService.createHtmlOutputFromFile('htmlss').setWidth(1000).setHeight(450);
      userInterface.append(s);
      SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Spreadsheet Data for ' + ss.getName() + ' Sheet: ' + sht.getName());
      break;
    case 'web':
      var userInterface=HtmlService.createHtmlOutputFromFile('htmlss').setWidth(1000).setHeight(450);
      return userInterface.append(s).setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
   }
}

function updateSpreadsheet(i,j,value)
{
  var ss=SpreadsheetApp.openById(SSID);
  var sht=ss.getSheetByName(sheetName);
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  rngA[i][j]=value;
  rng.setValues(rngA);
  var data = {'message':'Cell[' + Number(i + 1) + '][' + Number(j + 1) + '] Has been updated', 'ridx': i, 'cidx': j};
  return data;
}

function doGet()
{
  var output=htmlSpreadsheet('web');
  return output;
}

htmlss.html:

<!DOCTYPE html>
<html>
  <head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script>
    $(function() {

    });
    function updateSS(i,j)
    {
      var str='#txt' + String(i) + String(j);
      var value=$(str).val();
      $(str).css('background-color','#ffff00');
      google.script.run
         .withSuccessHandler(successHandler)
         .updateSpreadsheet(i,j,value)
    }
    function successHandler(data)
    {
      $('#success').text(data.message);
      $('#txt' + data.ridx + data.cidx).css('background-color','#ffffff');
    }
    console.log('My Code');
    </script>
    <style>
      th{text-align:left}
    </style>
  </head>
  <body>
  <div id="success"></div>

Upvotes: 2

user6655984
user6655984

Reputation:

If your web app works in the development mode ("Test web app for your latest code") but not when it's deployed, the problem is with how it is deployed.

Editing a script does not change anything in Web App functionality until the new version is deployed using Publish -> Deploy as web app, creating a new version of web app, and clicking "Deploy".

deploy

Upvotes: 2

Related Questions