I am currently working with a Google Sheet table that consists of 2 columns. The second column contains charges which can vary based on user input through a Google Form and are summed up using GAS. To view an example, click here.
The data from this Google Sheet table will be converted into HTML using GAS and sent as a report via email. During the conversion process, I need to filter out rows where the value in the second column is NOT zero. Thanks to @Tanaike, he provided a filtering script shown 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() != '-');
The filtering criteria mentioned above does not work when I change the currency format of the charges to a custom format similar to the default accounting format in Excel/Google Sheets. In this custom format, the currency symbol always appears on the far left of the cell wall, and the numbers are displayed from the far-right of the cell. The desired output in HTML format is shown below:
Image Link
Here is my HTML code for the 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 it possible to achieve this customization? I attempted to include the accounting.js library script in the HTML body, but it resulted in blank rows. Any assistance would be greatly appreciated. Thank you.