I got an error saying TypeError: String(...).setNumberFormat is not a function
by running the script on Google Sheets
I want to change the numeric format to #,###,### but i got an error instead after adding const osdNumberFormat = String(row[7]).setNumberFormat("0.000");
Here's the code:
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('Generate SP I - 1 PKS');
menu.addItem('Generate Now!', 'createNewGoogleDocs')
menu.addToUi();
}
function createNewGoogleDocs() {
//This value should be the id of your document template that we created in the last step
const googleDocTemplate = DriveApp.getFileById('xxxx');
//This value should be the id of the folder where you want your completed documents stored
const destinationFolder = DriveApp.getFolderById('xxxx')
//Here we store the sheet as a variable
const sheet = SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName('1 PKS')
//Now we get all of the values as a 2D array
const rows = sheet.getDataRange().getValues();
//Start processing each spreadsheet row
rows.forEach(function(row, index){
//Here we check if this row is the headers, if so we skip it
if (index === 0) return;
//Here we check if a document has already been generated by looking at 'Document Link', if so we skip it
if (row[11]) return;
//Using the row data in a template literal, we make a copy of our template document in our destinationFolder
const subjectDateTitle = new Date(row[10]).toLocaleDateString();
const copy = googleDocTemplate.makeCopy(`SP I - ${row[2]} - ${subjectDateTitle}` , destinationFolder)
//Once we have the copy, we then open it using the DocumentApp
const doc = DocumentApp.openById(copy.getId())
//All of the content lives in the body, so we get that for editing
const body = doc.getBody();
//In this line we do some friendly date formatting, that may or may not work for you locale
const friendlyDate = new Date(row[10]).toLocaleDateString();
const dueDateFormat = new Date(row[9]).toLocaleDateString();
const osdNumberFormat = String(row[7]).setNumberFormat("0.000");
//In these lines, we replace our replacement tokens with values from our spreadsheet row
body.replaceText('{{no_pks}}', row[0]);
body.replaceText('{{owner_name}}', row[1]);
body.replaceText('{{showroom_name}}', row[2]);
body.replaceText('{{address}}', row[3]);
body.replaceText('{{print_date}}', friendlyDate);
body.replaceText('{{subdistrict_name}}', row[4]);
body.replaceText('{{city_name}}', row[5]);
body.replaceText('{{province_name}}', row[6]);
body.replaceText('{{osd_num_amount}}', osdNumberFormat);
body.replaceText('{{osd_alp_amount}}', row[8]);
body.replaceText('{{due_date}}', dueDateFormat);
//We make our changes permanent by saving and closing the document
doc.saveAndClose();
//Store the url of our new document in a variable
const url = doc.getUrl();
//Write that value back to the 'Document Link' column in the spreadsheet.
sheet.getRange(index + 1, 12).setValue(url)
})
}
Any help will be greatly appreciated. Thanks in advance!
According to provided code, to get required number format, this line of code:
const osdNumberFormat = String(row[7]).setNumberFormat("0.000");
can be replaced with this line of code.
const osdNumberFormat = Number(row[7]).toLocaleString("en-US");
It can be checked, if it works.