从客户端html javascript运行的服务器端代码中更新电子表格不起作用 [英] update spreadsheet in serverside code run from client html javascript not working

问题描述

我有一个html，可在其中添加用户请求并输入数据.html主体中的javascript调用服务器端.我无法使用保存的ID或URL与工作表连接以添加行.

I have an html where user requests add and enters data. The javascript in the body of the html calls the server side. I am unable to connect with the sheet either with saved ID or URL in order to add the row.

尽管@Serge insas评论"openById的意思是"开放供读取和写入"，但我仍无法更新我的电子表格.我是在犯一个简单的错误，还是这不可能?从客户端启动的代码正在服务器中运行.

I cannot update of my spreadsheet despite @Serge insas comment that openById "it means "open for read and write". Am I making a simple mistake or is this impossible. The code initiated from the client side is running in the server.

const ssId = PropertiesService.getScriptProperties().getProperty('ssId');
var sheet = SpreadsheetApp.openById("[ssId]").getSheetByName('Sheet1');

const ssId = PropertiesService.getScriptProperties().getProperty('ssId');
var sheet = SpreadsheetApp.openById("ssId").getSheetByName('Sheet1');

两者均获取错误:异常:在对象SpreadsheetApp上获取方法或属性openById时发生意外错误.

Both get Error: Exception: Unexpected error while getting the method or property openById on object SpreadsheetApp.

  const ssUrl = PropertiesService.getScriptProperties().getProperty('ssUrl');
  var sheet = SpreadsheetApp.openByUrl("ssUrl").getSheetByName('Sheet1');

获取错误:异常:无效参数:url

Gets error: Exception: Invalid argument: url

以上是重要部分

/**
 *  this code is run from the javascript in the html dialog
 */
function addMbrCode(myAddForm)  {
//  removed logging 
  console.log("Beginning addMbrCode" );
  paragraph = body.appendParagraph('Beginning addMbrCode.');
  
  // Exception: Unexpected error while getting the method or property openById on object SpreadsheetApp.
//  const ssId = PropertiesService.getScriptProperties().getProperty('ssId');
//  var sheet = SpreadsheetApp.openById("[ssId]").getSheetByName('Sheet1');
//  var sheet = SpreadsheetApp.openById("ssId").getSheetByName('Sheet1');
  
// Exception: Invalid argument: url  
  const ssUrl = PropertiesService.getScriptProperties().getProperty('ssUrl');
  var sheet = SpreadsheetApp.openByUrl("ssUrl").getSheetByName('Sheet1');
  
  myAddForm =  [ fName, lName, inEmail, fallNum, winNum, sprNum];
  var fName = myAddForm[0];
  var lName = myAddForm[1];
  var inEmail = myAddForm[2];
  var fallNum = myAddForm[3];
  var winNum = myAddForm[4];
  var sprNum = myAddForm[5];
  
  var retCd = '';
  /**
   *  10 - successful add
   *  20 - duplicate - not added
   */  
  var combNameRng = sheet.getRange(2, 4, numRows).getValues();
  var inCName = (fName + '.' + lName).toString().toLowerCase();
  if (combNameRng.indexOf(inCName) > 0 )   {
    console.log("Alert: Not adding duplicate " 
                + fName + ' ' + lName + " retCd: " + 20 );
    paragraph = body.appendParagraph("Not adding duplicate " 
                                     + fName + ' ' + lName + " retCd: " + 20);
    retCd = 20;
    return retCd;
  }
  
  sheet.appendRow([fName.toString().toLowerCase()
                 , lName.toString().toLowerCase()
                 , 
                 , inEmail.toString().toLowerCase()
                 ]);
  const currRow = sheet.getLastRow().toString();

);

  retCd = 10;

  return retCd;
  
}

如果有所作为，这是对话框窗口中html正文中的javascript.

If this makes a difference, here is the javascript from the body of my html in the dialog window.

<script>
  document.querySelector("#myAddForm").addEventListener("submit", 
    function(e)
    {
      alert('begin addEventListener');
      e.preventDefault();    //stop form from submitting
      
      var retCd =  google.script.run.addMbrCode(this);   // client side validation

          document.getElementById('errMsg').textContent = 'Successful member 

      return false;  // do not submit - redisplay html
    }
  );
</script>

删除了不需要的编码细节

每个@iansedano，我创建了一个对象/数组来代替它使用，并添加了成功处理程序和失败处理程序.无论哪种情况，我都想再次在消息中看到html.这是当前的脚本.响应是如此混乱，我看不到警报，Logger.log或console.log.疯狂的购物者正在使用我的互联网！

Removed unneeded coding detail

Per @iansedano I created an object/array to use instead of this and added the successhandler and failurehandler. In either case I want to see the html again with my message. This is the current script. Response is so doggy I am not seeing alerts, Logger.log, or console.log. Crazy shoppers using my internet!

<script>   

  document.querySelector("#myRmvForm").addEventListener("submit", 
    function(e)
      // removed alerts and logging
      // removed client side validation for simplicity
      cSideValidate();

      // Then we prevent the form from being submitted by canceling the event
      event.preventDefault();
    });
       
  function cSideValidate()  {

    dataObj = [
      document.getElementById('fName').value,
      document.getElementById('lName').value,
      document.getElementById('email').value
    ];
    var retCd = google.script.run.withSuccessHandler(serverReply)
                .withFailureHandler(serverReply)
                .rmvMbrCode(dataObj);  // server side validation
  }
  
  function serverReply {
    // logic to set the correct message - this is an example
    document.getElementById('errMsg').textContent 
             = 'Successful delete using email.';
  }
  
</script>

我的电子表格中未添加任何内容，因此服务器端代码无法正常工作.我看到我的登录名，所以我知道它已经到了.

Nothing is being added to my spreadsheet so the server side code is not working. I see my loggin so I know it is getting there.

推荐答案

您正在从脚本属性中获取 ssId 并将其分配给 ssId 变量，但是您将字符串("ssId" )传递给 openById()函数，而不是变量的值.请尝试以下操作:

You're getting ssId from the script properties and assigning it to the ssId variable, but then you pass a string ("ssId") to the openById() function, not the value of the variable. Try the following please:

const ssId = PropertiesService.getScriptProperties().getProperty('ssId');
var sheet = SpreadsheetApp.openById(ssId).getSheetByName('Sheet1');

这篇关于从客户端html javascript运行的服务器端代码中更新电子表格不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！