英文:
Put a google sheets formula in a cell from Apps Script
问题
I'm using this code:
function doPost(e) {
var ss = SpreadsheetApp.openById('');
var sh = ss.getSheetByName('Hoja 1');
var data = Utilities.base64Decode(e.parameters.data);
var blob = Utilities.newBlob(data, e.parameters.mimetype, e.parameters.filename);
var fileId = DriveApp.getFolderById(e.parameters.folderId).createFile(blob).getId();
var lastfila= ss.getLastRow()+1;
const itemStock='='+ 'G' + lastfila+ '+' + 'SUMAR.SI.CONJUNTO(' +'Hoja 2'!D:D;'Hoja 2'!C:C;B"+lastfila+")";
var rowData = [];
rowData.push(e.parameters.itemName[0]);
rowData.push(e.parameters.itemDescription[0]);
rowData.push(e.parameters.itemCategory[0]);
rowData.push(e.parameters.filename[0]);
rowData.push(fileId);
rowData.push('https://drive.google.com/file/d/' + fileId);
rowData.push(e.parameters.itemUnit[0]);
rowData.push(e.parameters.itemLocation[0]);
rowData.push(itemStock);
sh.appendRow(rowData);
return ContentService.createTextOutput('Image: ' + e.parameters.filename + ' with ID: ' + fileId + ' successfully uploaded to Google Drive' );
}
对于你提到的问题，你需要确保 SUMAR.SI.CONJUNTO 函数的语法和引用范围正确。你可以检查单元格引用是否正确，确保 'Hoja 2'!D:D 和 'Hoja 2'!C:C 是有效的引用范围。如果问题仍然存在，可能需要检查你的电子表格中的其他因素，如数据格式或权限。
不过，请注意，你的代码中有一些占位符 '' 和 B"+lastfila+"，你可能需要替换这些占位符为有效的值。
英文:
I'm usign this code:
function doPost(e) {
var ss = SpreadsheetApp.openById('');
var sh = ss.getSheetByName('Hoja 1');
var data = Utilities.base64Decode(e.parameters.data);
var blob = Utilities.newBlob(data, e.parameters.mimetype, e.parameters.filename);
var fileId = DriveApp.getFolderById(e.parameters.folderId).createFile(blob).getId();
var lastfila= ss.getLastRow()+1;
const itemStock='='+'G'+lastfila+'+'+'SUMAR.SI.CONJUNTO('+"'Hoja 2'!D:D;'Hoja 2'!C:C;B"+lastfila+")";
var rowData = [];
rowData.push(e.parameters.itemName[0]);
rowData.push(e.parameters.itemDescription[0]);
rowData.push(e.parameters.itemCategory[0]);
rowData.push(e.parameters.filename[0]);
rowData.push(fileId);
rowData.push('https://drive.google.com/file/d/' + fileId);
rowData.push(e.parameters.itemUnit[0]);
rowData.push(e.parameters.itemLocation[0]);
rowData.push(itemStock);
sh.appendRow(rowData);
return ContentService.createTextOutput('Image: ' + e.parameters.filename + ' with ID: ' + fileId + ' successfully uploaded to Google Drive' );
}
for some reason when adding the row created from the code, the spreadsheet does not take the formula "SUM.SI.CONJUNTO()"(SUMIFS)
How can I solve it?
Thanks.
Find a way to send spreadsheet form from Apps Script
答案1
得分: 0
从您的脚本和错误信息来看，将 SUMAR.SI.CONJUNTO 更改为 SUMIFS 如何？当您的脚本被修改后，如下所示。
从：
const itemStock = '='+ 'G' + lastfila + '+' + 'SUMAR.SI.CONJUNTO(' + "'Hoja 2'!D:D;" + "'Hoja 2'!C:C;B" + lastfila + ")";
至：
const itemStock = '=G' + lastfila + '+SUMIFS(' + "'Hoja 2'!D:D;" + "'Hoja 2'!C:C;B" + lastfila + ")";
或
const itemStock = `=G${lastfila}+SUMIFS('Hoja 2'!D:D;'Hoja 2'!C:C;B${lastfila})`;
注意：
-
从您的错误信息图片来看，似乎出现了
Función desconocida: 'SIMIFS'.。我担心这是拼写错误。而且，在这种情况下，我担心您可能没有使用SUMAR.SI.CONJUNTO。如果您使用的是SIMIFS，请将SUMIFS或SUMAR.SI.CONJUNTO进行修改，并再次测试。 -
在这个答案中，我们假设您脚本的其他部分是有效的。请注意这一点。
参考链接：
英文:
From your script and your error message, how about changing SUMAR.SI.CONJUNTO to SUMIFS? When your script is modified, it becomes as follows.
From:
const itemStock='='+'G'+lastfila+'+'+'SUMAR.SI.CONJUNTO('+"'Hoja 2'!D:D;'Hoja 2'!C:C;B"+lastfila+")";
To:
const itemStock = '=' + 'G' + lastfila + '+' + 'SUMIFS(' + "'Hoja 2'!D:D;'Hoja 2'!C:C;B" + lastfila + ")";
or
const itemStock = `=G${lastfila}+SUMIFS('Hoja 2'!D:D;'Hoja 2'!C:C;B${lastfila})`;
Note:
-
When your image of the error message, it seems
Función desconocida: 'SIMIFS'.. I'm worried about misspellings. And also, in this case, I'm worried that you might not useSUMAR.SI.CONJUNTO. If you are usingSIMIFS, please modifySUMIFSorSUMAR.SI.CONJUNTO, and test it again. -
In this answer, it supposes that your other part of the script works. Please be careful about this.
References:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论