英文:
Create and use a function in Google App script
问题
我是相当新的在这个平台上，我也开始学习Google App脚本。我遇到了一个在App脚本中插入的公式问题，它不起作用。我试图自动提取周数、月份和年份，并将它们自动粘贴到每个数据输入（用户表单）的相应列中。
我使用了这个公式，但它不起作用：
// Boucle pour la semaine or Loop for the week
//var ss1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Base Données Production");
//ss1.getRange(lastRow_Basedonprod1, 11).setFormula('=IFERROR(VLOOKUP(A&+lastRow_Basedonprod1,Utilitaire!$C$21:$H$386,4),"")');
//ss1.getRange(lastRow_Basedonprod2, 11).setFormula('=IFERROR(VLOOKUP(A&+lastRow_Basedonprod2,Utilitaire!$C$21:$H$386,4),"")');
//ss1.getRange(lastRow_Basedonprod3, 11).setFormula('=IFERROR(VLOOKUP(A&+lastRow_Basedonprod3,Utilitaire!$C$21:$H$386,4),"")');
//ss1.getRange(lastRow_Basedonprod4, 11).setFormula('=IFERROR(VLOOKUP(A&+lastRow_Basedonprod4,Utilitaire!$C$21:$H$386,4),"")');
基本上，我想做的是，每次我在"Saisie production"选项卡中输入值时，我都会在名为"Base données Production"的新工作表中获取这些值。除了周、月和年的列（K、L、M）之外，其他都可以正常工作。
Google表格链接：
https://docs.google.com/spreadsheets/d/1F4-nzozPpmgP_QUtnUlbcnJPTgs-q7EmpPhuU2iUREk/edit?usp=sharing
英文:
I am quite new on this platform and I am starting also to learn Google App script. I have issues a formula I am trying to insert in App script which is not working. I am trying to extract the week number, month and year and paste them automatically in their columns at each data entry (userform)
I used this formula but it doesn't work
Sorry to mix it up with a bit of french but here is the code:
// Boucle pour la semaine or Loop for the week
//var ss1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Base Données Production");
//ss1.getRange( lastRow_Basedonprod1, 11 ).setFormula( '=IFERROR(VLOOKUP(A&+lastRow_Basedonprod1,Utilitaire!$C$21:$H$386,4),"")' );
//ss1.getRange( lastRow_Basedonprod2, 11 ).setFormula( '=IFERROR(VLOOKUP(A&+lastRow_Basedonprod2,Utilitaire!$C$21:$H$386,4),"")' );
//ss1.getRange( lastRow_Basedonprod3, 11 ).setFormula( '=IFERROR(VLOOKUP(A&+lastRow_Basedonprod3,Utilitaire!$C$21:$H$386,4),"")' );
//ss1.getRange( lastRow_Basedonprod4, 11 ).setFormula( '=IFERROR(VLOOKUP(A&+lastRow_Basedonprod4,Utilitaire!$C$21:$H$386,4),"")' );
Basically what I am trying to do is every time I enter values in "Saisie production" tab, I am getting those values in a new sheet called "Base données Production". It's working fine except for the week, month and year which are in columns K,L,M.
Google sheet link:
https://docs.google.com/spreadsheets/d/1F4-nzozPpmgP_QUtnUlbcnJPTgs-q7EmpPhuU2iUREk/edit?usp=sharing
答案1
得分: 1
你需要将变量中的值连接到公式字符串中。例如：
var lastRow_Basedonprod1 = 1; // 假设值为1
var formula = '=IFERROR(VLOOKUP(A' + lastRow_Basedonprod1 + ',Utilitaire!$C$21:$H$386,4),"")';
console.log( formula );
所以你的公式逻辑应该如下：
ss1.getRange( lastRow_Basedonprod1, 11 ).setFormula('=IFERROR(VLOOKUP(A'+ lastRow_Basedonprod1 + ',Utilitaire!$C$21:$H$386,4),"")');
ss1.getRange( lastRow_Basedonprod2, 11 ).setFormula('=IFERROR(VLOOKUP(A'+ lastRow_Basedonprod2 + ',Utilitaire!$C$21:$H$386,4),"")');
ss1.getRange( lastRow_Basedonprod3, 11 ).setFormula('=IFERROR(VLOOKUP(A'+ lastRow_Basedonprod3 + ',Utilitaire!$C$21:$H$386,4),"")');
ss1.getRange( lastRow_Basedonprod4, 11 ).setFormula('=IFERROR(VLOOKUP(A'+ lastRow_Basedonprod4 + ',Utilitaire!$C$21:$H$386,4),"")');
英文:
You will need to concatenate the value in your variables into the formula string. For example:
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-js -->
var lastRow_Basedonprod1 = 1; // Assuming that the value is 1
var formula = '=IFERROR(VLOOKUP(A' + lastRow_Basedonprod1 + ',Utilitaire!$C$21:$H$386,4),"")';
console.log( formula );
<!-- end snippet -->
So your formula logic should look like this
ss1.getRange( lastRow_Basedonprod1, 11 ).setFormula( '=IFERROR(VLOOKUP(A'+ lastRow_Basedonprod1 + ',Utilitaire!$C$21:$H$386,4),"")' );
ss1.getRange( lastRow_Basedonprod2, 11 ).setFormula( '=IFERROR(VLOOKUP(A'+ lastRow_Basedonprod2 + ',Utilitaire!$C$21:$H$386,4),"")' );
ss1.getRange( lastRow_Basedonprod3, 11 ).setFormula( '=IFERROR(VLOOKUP(A'+ lastRow_Basedonprod3 + ',Utilitaire!$C$21:$H$386,4),"")' );
ss1.getRange( lastRow_Basedonprod4, 11 ).setFormula( '=IFERROR(VLOOKUP(A'+ lastRow_Basedonprod4 + ',Utilitaire!$C$21:$H$386,4),"")' );
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论