google-maps - 如何将最后一行谷歌表格单元格公式值转换为变量并替换谷歌文档占位符

Question

我在 google word doc 中有一份租赁协议表格，我经常在其中更改字段。我创建了一个谷歌表单，我在其中接受输入并使用从帮助中获取的脚本编辑器。现在我有一个“RentAmount”字段，我在其中输入金额并希望自动转换金额，例如 $1000 应该转换为“一千”我还设法使用公式“https://www.xelplus”在谷歌表中转换.com/Excel-Formula-Convert-Numbers-to-Words/" 并在转换后我想获取变量中的公式值以将转换后的文本放置到 word doc 占位符。请帮忙

    function autoFillGoogleDocFromForm(e) {  
   var timestamp = e.values[0];
   var date_Dayof = e.values[1];
   var month = e.values[2];
   var ownername_MR_MRS_MS = e.values[3];
   var RentAmount = e.values[4];  
   var file = DriveApp.getFileById('fileid');  
   var folder = DriveApp.getFolderById('folderid')
   var copy = file.makeCopy(ownername_MR_MRS_MS, folder);  
   var doc = DocumentApp.openById(copy.getId());  
   var body = doc.getBody();
   body.replaceText("{{Date_Day_of}}", date_Dayof);
   body.replaceText('{{Month}}', month);
   body.replaceText("{{Owner_Name_MR/MRS/MS}}", ownername_MR_MRS_MS);
   body.replaceText('{{Rent Amount}}', RentAmount);
   doc.saveAndClose(); 
}

标签： google-mapsgoogle-apps-scriptgoogle-sheetsgoogle-cloud-platform

Answer 1

假设你的公式是

=CHOOSE(LEFT(T​​EXT(B3,"000000000.00"))+1,,"一","二","三","四","五","六","七","八", "九") &IF(--LEFT(T​​EXT(B3,"000000000.00"))=0,,IF(AND(--MID(TEXT(B3,"000000000.00"),2,1)=0,--MID (TEXT(B3,"000000000.00"),3,1)=0),"百","百和")) &CHOOSE(MID(TEXT(B3,"000000000.00"),2,1)+1,,, "二十","三十","四十","五十","六十","七十","八十","九十") &IF(--MID(TEXT(B3,"000000000.00"),2,1 )<>1,CHOOSE(MID(TEXT(B3,"000000000.00"),3,1)+1,,"一","二","三","四","五","六", “七”，"八","九"), CHOOSE(MID(TEXT(B3,"000000000.00"),3,1)+1,"十","十一","十二","十三","十四","十五","十六","十七","十八","十九")) &IF((--LEFT(T​​EXT(B3,"000000000.00"))+MID(TEXT(B3,"000000000.00"),2, 1)+MID(TEXT(B3,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(B3,"000000000.00"),4,1)+MID(TEXT( B3,"000000000.00"),5,1)+MID(TEXT(B3,"000000000.00"),6,1)+MID(TEXT(B3,"000000000.00"),7,1))=0,(-- MID(TEXT(B3,"000000000.00"),8,1)+RIGHT(TEXT(B3,"000000000.00")))>0),"百万和","百万")) &CHOOSE(MID(TEXT(B3, "000000000.00"),4,1)+1,,"一","二","三","四","五","六","七","八","九") &IF(--MID(TEXT(B3,"000000000.00"),4,1)=0,, IF(AND(--MID(TEXT(B3,"000000000.00"),5,1)=0,--MID(TEXT(B3,"000000000.00"),6,1)=0),"百","百和")) &CHOOSE(MID(TEXT(B3,"000000000.00"),5,1)+1,,,"二十","三十","四十","五十","六十","七十" ,"八十","九十") &IF(--MID(TEXT(B3,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(B3,"000000000.00"),6,1)+ 1,,"一","二","三","四","五","六","七","八","九"),CHOOSE(MID(TEXT(B3,"000000000 .00"),6,1)+1,"十","十一","十二","十三","十四","十五","十六","十七","十八","十九" )) &IF((--MID(TEXT(B3,"000000000.00"),4,1)+MID(TEXT(B3,"000000000.00"),5,1)+MID(TEXT(B3,"000000000.00"), 6,1))=0,,IF(OR((--MID(TEXT(B3,"000000000.00"),7,1)+MID(TEXT(B3,"000000000.00"),8,1)+MID( TEXT(B3,"000000000.00"),9,1))=0,--MID(TEXT(B3,"000000000.00"),7,1)<>0),"千","千和")) &选择(MID(TEXT(B3,"000000000.00"),7,1)+1,,"一","二","三","四","五","六","七","八","九") &IF(--MID(TEXT(B3,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(B3,"000000000.00"),8,1)=0,--MID(TEXT(B3,"000000000.00"), 9,1)=0),"百","百和"))& CHOOSE(MID(TEXT(B3,"000000000.00"),8,1)+1,,,"二十","三十","四十","五十","六十","七十","八十","九十") &IF(--MID(TEXT(B3,"000000000.00"),8,1)<>1,CHOOSE(MID( TEXT(B3,"000000000.00"),9,1)+1,,"一","二","三","四","五","六","七","八","九"),CHOOSE(MID(TEXT(B3,"000000000.00"),9,1)+1,"十","十一","十二","十三","十四","十五","十六","十七岁","十八岁","十九岁")),

您可以将其实现到现有代码中，如下所示：

function autoFillGoogleDocFromForm(e) {  
  var timestamp = e.values[0];
  var date_Dayof = e.values[1];
  var month = e.values[2];
  var ownername_MR_MRS_MS = e.values[" + row + "];
  var RentAmount = e.values[4]; 
  var column = 20; // column T
  var row = e.range.getRow();
  var sheet = e.range.getSheet();
  sheet.getRange(row, column).setFormula("=CHOOSE(LEFT(TEXT(B" + row + ",\"000000000.00\"))+1,,\"One\",\"Two\",\"Three\",\"Four\",\"Five\",\"Six\",\"Seven\",\"Eight\",\"Nine\")\r\n&IF(--LEFT(TEXT(B" + row + ",\"000000000.00\"))=0,,IF(AND(--MID(TEXT(B" + row + ",\"000000000.00\"),2,1)=0,--MID(TEXT(B" + row + ",\"000000000.00\")," + row + ",1)=0),\" Hundred\",\" Hundred and \"))\r\n&CHOOSE(MID(TEXT(B" + row + ",\"000000000.00\"),2,1)+1,,,\"Twenty \",\"Thirty \",\"Forty \",\"Fifty \",\"Sixty \",\"Seventy \",\"Eighty \",\"Ninety \")\r\n&IF(--MID(TEXT(B" + row + ",\"000000000.00\"),2,1)<>1,CHOOSE(MID(TEXT(B" + row + ",\"000000000.00\")," + row + ",1)+1,,\"One\",\"Two\",\"Three\",\"Four\",\"Five\",\"Six\",\"Seven\",\"Eight\",\"Nine\"),\r\nCHOOSE(MID(TEXT(B" + row + ",\"000000000.00\")," + row + ",1)+1,\"Ten\",\"Eleven\",\"Twelve\",\"Thirteen\",\"Fourteen\",\"Fifteen\",\"Sixteen\",\"Seventeen\",\"Eighteen\",\"Nineteen\"))\r\n&IF((--LEFT(TEXT(B" + row + ",\"000000000.00\"))+MID(TEXT(B" + row + ",\"000000000.00\"),2,1)+MID(TEXT(B" + row + ",\"000000000.00\")," + row + ",1))=0,,IF(AND((--MID(TEXT(B" + row + ",\"000000000.00\"),4,1)+MID(TEXT(B" + row + ",\"000000000.00\"),5,1)+MID(TEXT(B" + row + ",\"000000000.00\"),6,1)+MID(TEXT(B" + row + ",\"000000000.00\"),7,1))=0,(--MID(TEXT(B" + row + ",\"000000000.00\"),8,1)+RIGHT(TEXT(B" + row + ",\"000000000.00\")))>0),\" Million and \",\" Million \"))\r\n&CHOOSE(MID(TEXT(B" + row + ",\"000000000.00\"),4,1)+1,,\"One\",\"Two\",\"Three\",\"Four\",\"Five\",\"Six\",\"Seven\",\"Eight\",\"Nine\")\r\n&IF(--MID(TEXT(B" + row + ",\"000000000.00\"),4,1)=0,,IF(AND(--MID(TEXT(B" + row + ",\"000000000.00\"),5,1)=0,--MID(TEXT(B" + row + ",\"000000000.00\"),6,1)=0),\" Hundred\",\" Hundred and\"))\r\n&CHOOSE(MID(TEXT(B" + row + ",\"000000000.00\"),5,1)+1,,,\" Twenty\",\" Thirty\",\" Forty\",\" Fifty\",\" Sixty\",\" Seventy\",\" Eighty\",\" Ninety\")\r\n&IF(--MID(TEXT(B" + row + ",\"000000000.00\"),5,1)<>1,CHOOSE(MID(TEXT(B" + row + ",\"000000000.00\"),6,1)+1,,\" One\",\" Two\",\" Three\",\" Four\",\" Five\",\" Six\",\" Seven\",\" Eight\",\" Nine\"),CHOOSE(MID(TEXT(B" + row + ",\"000000000.00\"),6,1)+1,\" Ten\",\" Eleven\",\" Twelve\",\" Thirteen\",\" Fourteen\",\" Fifteen\",\" Sixteen\",\" Seventeen\",\" Eighteen\",\" Nineteen\"))\r\n&IF((--MID(TEXT(B" + row + ",\"000000000.00\"),4,1)+MID(TEXT(B" + row + ",\"000000000.00\"),5,1)+MID(TEXT(B" + row + ",\"000000000.00\"),6,1))=0,,IF(OR((--MID(TEXT(B" + row + ",\"000000000.00\"),7,1)+MID(TEXT(B" + row + ",\"000000000.00\"),8,1)+MID(TEXT(B" + row + ",\"000000000.00\"),9,1))=0,--MID(TEXT(B" + row + ",\"000000000.00\"),7,1)<>0),\" Thousand \",\" Thousand and \"))\r\n&CHOOSE(MID(TEXT(B" + row + ",\"000000000.00\"),7,1)+1,,\"One\",\"Two\",\"Three\",\"Four\",\"Five\",\"Six\",\"Seven\",\"Eight\",\"Nine\")\r\n&IF(--MID(TEXT(B" + row + ",\"000000000.00\"),7,1)=0,,IF(AND(--MID(TEXT(B" + row + ",\"000000000.00\"),8,1)=0,--MID(TEXT(B" + row + ",\"000000000.00\"),9,1)=0),\" Hundred \",\" Hundred and \"))&\r\nCHOOSE(MID(TEXT(B" + row + ",\"000000000.00\"),8,1)+1,,,\"Twenty \",\"Thirty \",\"Forty \",\"Fifty \",\"Sixty \",\"Seventy \",\"Eighty \",\"Ninety \")\r\n&IF(--MID(TEXT(B" + row + ",\"000000000.00\"),8,1)<>1,CHOOSE(MID(TEXT(B" + row + ",\"000000000.00\"),9,1)+1,,\"One\",\"Two\",\"Three\",\"Four\",\"Five\",\"Six\",\"Seven\",\"Eight\",\"Nine\"),CHOOSE(MID(TEXT(B" + row + ",\"000000000.00\"),9,1)+1,\"Ten\",\"Eleven\",\"Twelve\",\"Thirteen\",\"Fourteen\",\"Fifteen\",\"Sixteen\",\"Seventeen\",\"Eighteen\",\"Nineteen\"))");
  var formulaValue = sheet.getRange(row, column).getDisplayValue();
  Logger.log(formulaValue);
  // do what your want with formulaValue
  ...
}