Search code examples
javascripthtmlcssgoogle-apps-scriptnumber-formatting

Any html code that can apply the same currency format that we have customized in google sheet/GAS?

I have a table of 2 columns in google sheet. 2nd column consists of charges which the values may vary based on user input via google form and sum up using GAS. xtrasvc
This gsheet table will then be converted into html with GAS and emailed as a report. When converting the gsheet table into html, the rows of data is filtered to display only rows of data which the 2nd column is not zero value. Thanks to @Tanaike,filter script as below:

var tableRangeValues=xtraqsheet.getRange(2,1,5,2).getDisplayValues();

var forhtmlemail=HtmlService.createTemplateFromFile("extrasvcitem");

forhtmlemail.tableRangeValues = tableRangeValues.filter(([,b]) => b.toString()!=0 && b.toString()!='0' && b.toString()!='' && b.toString()!='RM - ' && b.toString()!='-');

Filter criterias declared in the above line of code doesn't seem to work when I change the currency format of the charges to a custom format which is the same as default accounting format in excel/gsheet, where the currency symbol would always shows on the far left of the cell wall and the numbers would always be shown from the far-right of the cell. The output I expected is as below (in html format). I want to maintain that same custom format and just show the non-zero data rows:-
expectedoutput

my html code for this table :-

<tbody style="border-style: none solid solid;border-color: #3E1176">

<?tableRangeValues.forEach((r,i)=>{
let color;
if(i%2===0){color="white"}else{color="#F6EFFE"}?>
<tr style="border-style: none solid;border-color:#3E1176;padding:10px;color:#3E1176;font-size:10pt;background-color:<?=color?>;">
      <td style="width: 52.7272%;"><?=r[0]?></td>
      <td style="width: 36.0039%;"><?=r[1]?></td>
</tr>
<?})?>
</tbody>

Is this achievable? I've tried to insert the accounting.js library script in the html body but it resulted into blanks for all rows. Would appreciate some help. Thanks.

Solution

  • I believe your goal as follows.

    • You want to achieve the conversion from the top image to the bottom image using Google Apps Script.
    • In your above Spreadsheet, the column "B" is formatted by the number format.

    Modification points:

    • Unfortunately, I cannot understand about your whole script. But I guess that you might be retrieving the values using getValues(). In that case, I think that the formatted values are not kept. So in your case, I would like to propose to use getDisplayValues() instead of getValues(). By this, the formatted values are kept.
      • When getDisplayValues() is used, the values can be checked using the regex.
    • And, in your HTML, the tag of <table></table> is not used.

    When above points are reflected to your script, it becomes as follows.

    Modified script:

    Google Apps Script side:

    const sheetName = "Sheet1"; // Please set the sheet name.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
const tableRangeValues = sheet.getRange("A2:B9").getDisplayValues();
const forhtmlemail=HtmlService.createTemplateFromFile("i17");
forhtmlemail.tableRangeValues = tableRangeValues.filter(([,b]) => !(/^[RM -]+$/.test(b)));
const res = forhtmlemail.evaluate().getContent();
console.log(res)
    • In this sample script, the values are retrieved from the cells "A2:B9" on "Sheet1". So please modify this for your actual situation.
    • If you want to include the header, please modify A2:B9 to A1:B9.

    HTML & Javascript side:

    <tbody style="border-style: none solid solid;border-color: #3E1176">
  <table>
    <?tableRangeValues.forEach((r,i,a)=>{
let color;
if(i%2===0){color="white"}else{color="#F6EFFE"}?>
    <tr style="border-style: none solid;border-color:#3E1176;padding:10px;color:#3E1176;font-size:10pt;background-color:<?=color?>;">
      <td style="width: 52.7272%;" align="<? if (i == a.length - 1) {?> <?='right'?> <? } else { ?> <?='left'?> <?}?>"><?=r[0]?></td>
      <td style="width: 36.0039%; text-align-last: justify;">
        <?!=r[1]?>
      </td>
    </tr>
    <?})?>
  </table>
</tbody>

    Result:

    When above modified script is used, the following result can be obtained.

    <tbody style="border-style: none solid solid;border-color: #3E1176">
  <table>
    <tr style="border-style: none solid;border-color:#3E1176;padding:10px;color:#3E1176;font-size:10pt;background-color:white;">
      <td style="width: 52.7272%;" align=" left ">Custom packaging request</td>
      <td style="width: 36.0039%; text-align-last: justify;"> RM 12,345.00 </td>
    </tr>
    <tr style="border-style: none solid;border-color:#3E1176;padding:10px;color:#3E1176;font-size:10pt;background-color:#F6EFFE;">
      <td style="width: 52.7272%;" align=" left ">Change of license plate</td>
      <td style="width: 36.0039%; text-align-last: justify;"> RM 1,234.00 </td>
    </tr>
    <tr style="border-style: none solid;border-color:#3E1176;padding:10px;color:#3E1176;font-size:10pt;background-color:white;">
      <td style="width: 52.7272%;" align=" left ">Car wash mobile service</td>
      <td style="width: 36.0039%; text-align-last: justify;"> RM 123.00 </td>
    </tr>
    <tr style="border-style: none solid;border-color:#3E1176;padding:10px;color:#3E1176;font-size:10pt;background-color:#F6EFFE;">
      <td style="width: 52.7272%;" align=" left ">Personal drive service</td>
      <td style="width: 36.0039%; text-align-last: justify;"> RM 20.00 </td>
    </tr>
    <tr style="border-style: none solid;border-color:#3E1176;padding:10px;color:#3E1176;font-size:10pt;background-color:white;">
      <td style="width: 52.7272%;" align=" left ">Change of security ID</td>
      <td style="width: 36.0039%; text-align-last: justify;"> RM 2.00 </td>
    </tr>
    <tr style="border-style: none solid;border-color:#3E1176;padding:10px;color:#3E1176;font-size:10pt;background-color:#F6EFFE;">
      <td style="width: 52.7272%;" align=" right ">TOTAL</td>
      <td style="width: 36.0039%; text-align-last: justify;"> RM 13,724.00 </td>
    </tr>
  </table>
</tbody>

    Note:

    • In above script, it supposes that the column "B" of your sample Spreadsheet is set by the number format like RM 12,345.00. If this was not correct, above script cannot be used. Please be careful this.

    References: