更新服务器端代码中的电子表格从客户端运行html javascript不工作

Question

我有一个html，用户在其中请求添加和输入数据。html正文中的javascript调用服务器端。我无法使用已保存的ID或URL连接到工作表以添加行。

尽管@Serge insas评论说openById"；它的意思是"；打开以进行读写"；。我是在犯一个简单的错误，还是这是不可能的。从客户端启动的代码正在服务器中运行。

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时出现意外错误。

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

获取错误：异常：无效参数：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。

<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，我创建了一个对象/数组来代替它，并添加了successhandler和failurehandler。在任何一种情况下，我都希望在我的消息中再次看到html。这是当前的脚本。反应太狗了，我没有看到警报、Logger.log或console.log。疯狂的购物者在使用我的互联网！

<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>

我的电子表格中没有添加任何内容，因此服务器端代码无法工作。我看到了我的日志，所以我知道它正在到达那里。

Answer 1

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

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