CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
user12742603
user12742603

Reputation:

Format Currency Value Apps Script on Spreadsheet

with this script in my google sheets (that generate automatically a doc based on row selected), for the currency value, return the values like 10245,1326666667 (on the sheet i have applied on the columns with the currency numbers a function that format the number as € xxx.xxx). How can i generate a value like € xxx.xxx,xx for the currency ?

var TEMPLATE_ID = ''

var PDF_FILE_NAME = ''

function onOpen() {

  SpreadsheetApp
    .getUi()
    .createMenu('CONTRATTO PSB  vecchi clienti')
    .addItem('Genera CONTRATTO PSB  vecchi clienti', 'createPdf')
    .addToUi()

}

function createPdf() {

  if (TEMPLATE_ID === '') {
    
    SpreadsheetApp.getUi().alert('TEMPLATE_ID needs to be defined in code.gs')
    return
  }
  
  var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy(),
      copyId = copyFile.getId(),
      copyDoc = DocumentApp.openById(copyId),
      copyBody = copyDoc.getActiveSection(),
      activeSheet = SpreadsheetApp.getActiveSheet(),
      numberOfColumns = activeSheet.getLastColumn(),
      activeRowIndex = activeSheet.getActiveRange().getRowIndex(),
      activeRow = activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns).getValues(),
      headerRow = activeSheet.getRange(1, 1, 1, numberOfColumns).getValues(),
      columnIndex = 0
 
  for (;columnIndex < headerRow[0].length; columnIndex++) {
    
    copyBody.replaceText('%' + headerRow[0][columnIndex] + '%', 
                         activeRow[0][columnIndex])                         
  }
    
  copyDoc.saveAndClose()

  /** var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'))  

  * if (PDF_FILE_NAME !== '') {
  
    newFile.setName(PDF_FILE_NAME)
  } */
  
  // copyFile.setTrashed(true)
  
  SpreadsheetApp.getUi().alert('Contratto creato!')
  
}

the code with Cooper version:

var TEMPLATE_ID = 'xxxx'

var PDF_FILE_NAME = ''

function onOpen() {

  SpreadsheetApp
    .getUi()
    .createMenu('xxxx')
    .addItem('xxxx', 'createPdf')
    .addToUi()

}

function createPdf() {
  if (TEMPLATE_ID === '') {
    SpreadsheetApp.getUi().alert('TEMPLATE_ID needs to be defined in code.gs');
    return;
  }
  var copyFile=DriveApp.getFileById('TEMPLATE_ID').makeCopy();
  const copyDoc=DocumentApp.openById(copyFile.getId());
  const copyBody=copyDoc.getActiveSection();
  const activeSheet=SpreadsheetApp.getActiveSheet();
  const numberOfColumns=activeSheet.getLastColumn();
  const activeRowIndex=activeSheet.getActiveRange().getRowIndex();
  activeSheet.getRange(activeRowIndex,1,1,numberOfColumns).setNumberFormat("[$€]#,##0.00");
  const activeRow=activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns).getDisplayValues();
  const headerRow=activeSheet.getRange(1, 1, 1, numberOfColumns).getValues();
  for (let columnIndex=0;columnIndex < headerRow[0].length; columnIndex++) {
    copyBody.replaceText('%' + headerRow[0][columnIndex] + '%', activeRow[0][columnIndex]);                         
  }
  copyDoc.saveAndClose();
  SpreadsheetApp.getUi().alert('Contratto creato!');
}

Upvotes: 0

Views: 598

Answers (1)

Cooper
Cooper

Reputation: 64082

For non V8

function createPdf() {
  if (TEMPLATE_ID === '') {
    SpreadsheetApp.getUi().alert('TEMPLATE_ID needs to be defined in code.gs');
    return;
  }
  var copyFile=DriveApp.getFileById(TEMPLATE_ID).makeCopy();
  var copyDoc=DocumentApp.openById(copyFile.getId());
  var copyBody=copyDoc.getActiveSection();
  var activeSheet=SpreadsheetApp.getActiveSheet();
  var numberOfColumns=activeSheet.getLastColumn();
  var activeRowIndex=activeSheet.getActiveRange().getRowIndex();
  activeSheet.getRange(activeRowIndex,1,1,numberOfColumns).setNumberFormat("[$€]#,##0.00");
  var activeRow=activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns).getDisplayValues();
  var headerRow=activeSheet.getRange(1, 1, 1, numberOfColumns).getValues();
  for (var columnIndex=0;columnIndex < headerRow[0].length; columnIndex++) {
    copyBody.replaceText('%' + headerRow[0][columnIndex] + '%', activeRow[0][columnIndex]);                         
  }
  copyDoc.saveAndClose();
  SpreadsheetApp.getUi().alert('Contratto creato!');
}

Upvotes: 1

Related Questions