How to get last row google sheet cell formula value into variable and replace the google doc place holder

1
votes

I have a tenancy agreement form in google word doc where I have frequently change fields. I had created a google form where I take input and using script editor which i had taken from help. Now I have a "RentAmount" field where I input amount and want to automatically convert the Amount in words like $1000 should convert in "One Thousand" I also manage to convert in google sheet by using the formula "https://www.xelplus.com/Excel-Formula-Convert-Numbers-to-Words/" and after converting I want to get the formula value in variable to place the converted text to word doc place holder. Please help

    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(); 
}
1
google-mapsgoogle-apps-scriptgoogle-sheetsgoogle-cloud-platform
Please explain which is the step where you need help. Is there any issue wiht your code? Any error message? Can you retrieve the e.values correctly? – ziganotschka
Thanks for the reply. I have no issue with the code it is working properly. I just need help to modify code to get the formula value to store in a variable and replace in google sheet place holder. please refer my requirement again. – Faisal Ijaz
So you have an additional column in your spreadsheet that takes in RentAmount and outputs the value in words after applying the formula? In which column ist his formula located? – ziganotschka
Thank you for the reply. The column is "T" – Faisal Ijaz

1 Answers

0
votes

Assuming that your formula is

=CHOOSE(LEFT(TEXT(B3,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &IF(--LEFT(TEXT(B3,"000000000.00"))=0,,IF(AND(--MID(TEXT(B3,"000000000.00"),2,1)=0,--MID(TEXT(B3,"000000000.00"),3,1)=0)," Hundred"," Hundred and ")) &CHOOSE(MID(TEXT(B3,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ") &IF(--MID(TEXT(B3,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(B3,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"), CHOOSE(MID(TEXT(B3,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")) &IF((--LEFT(TEXT(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)," Million and "," Million ")) &CHOOSE(MID(TEXT(B3,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &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)," Hundred"," Hundred and")) &CHOOSE(MID(TEXT(B3,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety") &IF(--MID(TEXT(B3,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(B3,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(B3,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen")) &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)," Thousand "," Thousand and ")) &CHOOSE(MID(TEXT(B3,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine") &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)," Hundred "," Hundred and "))& CHOOSE(MID(TEXT(B3,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ") &IF(--MID(TEXT(B3,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(B3,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(B3,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")),

you can implement it into your existing code as following:

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