Anyone good with javascript?

[tuscanyswe]

Im not, actually i dont know very much at all. I would say im on the level of someone who just bought their first dropbar equipped bike and had a few training rides.. Even tho that is the case im still thinking i can likely compete in a grand tour with a bit of practise..

And in that spirit im trying to get a google sheet to do all my invoices for me at the press on a button. Since the script i stole was not designed to be compatible with my sheets specifcly, there are concearns, most have been ironed out but for some reason the function gets stuck on a get date issue. So the script / function can only do one invoice from the sheet-list before it crash since it cant find or write the date in the specified places (or at least thats what i think is happening but the reality could be that im riding tubulars with an innertube as only means of fixing a puncture..) What do i know??.


So this is the error message i get "TypeError: Cannot find function getMonth in object"

This is the code suspected of failing me

"if (key === "date") {
// Invoice Date
invoiceDate = (values.getMonth() + 1) + "-" + values.getDate() + "-" + values.getFullYear();
replace('%date%', invoiceDate, docBody); // Write data"


So whats going wrong here?

[Dude]

pulled from here: https://www.w3schools.com/jsref/jsref_getmonth.asp or if you hate w3 schools irrationally like other devs you can get it here too: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/getMonth

I'm a pretty good "JV" developer, not varsity level though.

A few questions...when you console.log(values) what does it return? it should be a date object.

your code seems to work fine when 'values' is defined correctly. This works in my browser console.
var values = new Date;
invoiceDate = (values.getMonth() + 1) + "-" + values.getDate() + "-" + values.getFullYear();
//replace(param1, param2, param3) isn't defined
//console log = '10-25-2018'

[Dude]

ooOOOooo...i just read the first two paragraphs of your post...my response probably doesn't help all that much.

[deechee]

kind of hard without seeing everything, but seems like your getMonth isn't defined or accessible from the class.

Run through a debugger like intellij and it'll probably help you (ie. highlight if the function isn't recognized etc.) Intellij should be available as a trial.

[tuscanyswe]

Well actually it did help, as i did not know i could get automated help with debugging the code from various sources including console..

So that will likely help me in general once im more in to this.

But i have through trial an error (perhaps wrongly) concluded that its this string of code that somehow is not perfect or perhaps not compatible with my sheet and its settings.

Thing is the sheet give me the error MONTH message when i type it like this:

invoiceDate = (values.getMonth() + 1) + "-" + values.getDate() + "-" + values.getFullYear();
replace('%date%', invoiceDate, docBody);


And sheet give me the error YEAR message when i type it like this (only thing i changed in code):

invoiceDate = (values.getFullYear() + "-" + values.getDate() + "-" + values.getMonth() + 1);


So i get 2 different errors depending on how i type that string which (for me) makes me think that the error was in the code but perhaps its in the actual document or some compatability issues since im swedish and running some formats in google sheets like date in swedish version which is YYYY-MM-DD.
I have no idea if this could be causing issues but when i switch the code around it gives me different error messages.

But i guess this could also mean that somewhere in this process its trying to name something in this Date format and it cant do so and just leaves me with the first function it fails, either month or year in this case and perhaps thats not indicative of the code beeing wrong initself just that it cant replace or name the information as requested for some reason?


This is the entire script if that helps:

/*================================================= ================================================== =============*
Invoice Generator
================================================== ================================================== ============
Version: 1.0.0
Project Page: https://github.com/Sheetgo/invoice-generator
Copyright: (c) 2018 by Sheetgo

License: GNU General Public License, version 3 (GPL-3.0)
http://www.opensource.org/licenses/gpl-3.0.html
----------------------------------------------------------------------------------------------------------------
Changelog:

1.0.0 Initial release
*================================================= ================================================== =============*/

/**
* Project Settings
* @type {JSON}
*/
SETTINGS = {

// Spreadsheet Url (template filled with your data)
spreadsheetUrl: "<https://docs.google.com/spreadsheets/d/1NBEPoO_MchUlATvX5OXvIpCZ_alaKLawPja9Qgt_Cmw/edit?usp=sharing>",

// Spreadsheet name
sheetName: "Data",

// Document Url
documentUrl: "<https://docs.google.com/document/d/1mjs7bJ2dGRthi4CwyLEVF9GoSzoqZTkHDW28fNxU2m8/edit?usp=sharing>",

// Folder id
folderUrl: "https://drive.google.com/drive/folders/0B9O67uHE5cUJMUdqYVlnQ0NIQTg"

};


/**
* This funcion will run when you open the spreadsheet. It creates a Spreadsheet menu option to run the spript
*/
function onOpen() {
// Adds a custom menu to the spreadsheet.
SpreadsheetApp.getUi()
.createMenu('Invoice Generator')
.addItem('Generate Invoices', 'sendInvoice')
.addToUi();
}


/**
* Reads the spreadsheet data and creates the PDF invoice
*/
function sendInvoice() {
// Opens the spreadsheet and access the tab containing the data
var ss = SpreadsheetApp.openByUrl(SETTINGS.spreadsheetUrl);
Logger.log("apa", SETTINGS.folderUrl)
var dataSheet = ss.getSheetByName(SETTINGS.sheetName);

// Gets all values from the instanciated tab
var sheetValues = dataSheet.getDataRange().getValues();

// Gets the PDF url column index
var pdfIndex = sheetValues[0].indexOf("PDF Url");

// Gets the user's name (will be used as the PDF file name)
var clientNameIndex = sheetValues[0].indexOf("client_name");

var counter, invoiceNumCount, pdfInvoice, invoiceId, key, values, pdfName, invoiceNumber, invoiceDate;
for (var i = 1; i < sheetValues.length; i++) {

// Creates the Invoice
if (!sheetValues[i][pdfIndex]) {

// Duplicate teh template on Google Drive to manipulate the data
invoiceId = DriveApp.getFileById(SETTINGS.documentUrl.substrin g((SETTINGS.documentUrl.indexOf("/d/")+3), (SETTINGS.documentUrl.indexOf("/d/")+3+44))).makeCopy('Template Copy').getId();

// Instantiate the document
var docBody = DocumentApp.openById(invoiceId).getBody();

// Iterates over the spreadsheet columns to get the values used to write the document
for (var j = 0; j < sheetValues[i].length; j++) {

// Key and Values to be replaced
key = sheetValues[0][j];
values = sheetValues[i][j];

if (key === "date") {
// Invoice Date
invoiceDate = (values.getMonth() + 1) + "-" + values.getDate() + "-" + values.getFullYear();
replace('%date%', invoiceDate, docBody); // Write data



} else if (values) {
// Everything else appart from date values
if (key.indexOf("price") > -1 || key === "discount" || key.indexOf("total") > -1) {
replace('%' + key + '%', '' + values.toFixed(2), docBody); // Replace values
} else if (key === "tax_id") {
replace('%' + key + '%', "Org. nr. " + values, docBody); // Replace the tax_id
} else {
replace('%' + key + '%', values, docBody)
}

} else {
replace('%' + key + '%', '', docBody) // Replace empty string
}
}

// Get last invoice count from the tab 'Count'
counter = ss.getSheetByName('Count').getRange('A2');
invoiceNumCount = counter.getValue() + 1;

invoiceNumber = invoiceNumCount.padLeft(4, '0') + "-" + invoiceDate.split("-")[2];
replace('%number%', invoiceNumber, docBody);

// Rename the invoice document
pdfName = sheetValues[i][clientNameIndex] + " " + invoiceNumber;
DocumentApp.openById(invoiceId).setName(pdfName).s aveAndClose();

// Convert the Invoice Document into a PDF file
pdfInvoice = convertPDF(invoiceId);
// Set the PDF url into the spreadsheet
dataSheet.getRange(i + 1, pdfIndex + 1).setValue(pdfInvoice[0]);

// Update invoice counter
counter.setValue(invoiceNumCount);

// Delete the original document (will leave only the PDF)
Drive.Files.remove(invoiceId);
}
}
}


/**
* Convert a Google Docs into a PDF file
* @param {string} id - File Id
* @returns {*[]}
*/
function convertPDF(id) {
var doc = DocumentApp.openById(id);
var docBlob = doc.getAs('application/pdf');
docBlob.setName(doc.getName() + ".pdf"); // Add the PDF extension
var invFolder = SETTINGS.folderUrl.substring((SETTINGS.folderUrl.i ndexOf("/folders/")+9));
var file = DriveApp.getFolderById(invFolder).createFile(docBl ob);
var url = file.getUrl();
return [url, file.getId()];
}


/**
* Replace the document key/value
* @param {String} key - The document key to be replaced
* @param {String} text - The document text to be inserted
* @param {Body} body - the active document's Body.
* @returns {Element}
*/
function replace(key, text, body) {
return body.editAsText().replaceText(key, text);
}


/**
* Returns a new string that right-aligns the characters in this instance by padding them with any string on the left,
* for a specified total length.
* @param {Number} n - Number of characters to pad
* @param {String} str - The string to be padded
* @returns {string}
*/
Number.prototype.padLeft = function (n, str) {
return Array(n - String(this).length + 1).join(str || '0') + this;
};

[Dude]

I'm assuming you're using this invoice generator (https://blog.sheetgo.com/google-cloud-solutions/invoice-generator/). If so, i think your spreadsheet isn't setup correctly OR something about the tab it's pulling data from isn't set up correctly.

[tuscanyswe]

I'm assuming you're using this invoice generator (https://blog.sheetgo.com/google-cloud-solutions/invoice-generator/). If so, i think your spreadsheet isn't setup correctly OR something about the tab it's pulling data from isn't set up correctly.

Yes that is correct.

Thank for the suggestion. Yeah that quite possibly could be it considering the date format changes depending on if doc saved as us or swedish version for instance.

hmm Yeah well, it wont be easy to find the actual problem. The function is actually working now it just gives me the error code after the last invoice but it can still do them all (around 30). It does however not clean up the last documents from which it creates the last in line pdf-invoice. I guess that could be indicative of where the problem lies. Il start to look there.

I could spend a lot of time debugging this before i accidently find issue but i can also use it as is so thats good :)

[OtayBW]

Well actually it did help, as i did not know i could get automated help with debugging the code from various sources including console..

So that will likely help me in general once im more in to this.

But i have through trial an error (perhaps wrongly) concluded that its this string of code that somehow is not perfect or perhaps not compatible with my sheet and its settings.

Thing is the sheet give me the error MONTH message when i type it like this:

invoiceDate = (values.getMonth() + 1) + "-" + values.getDate() + "-" + values.getFullYear();
replace('%date%', invoiceDate, docBody);


And sheet give me the error YEAR message when i type it like this (only thing i changed in code):

invoiceDate = (values.getFullYear() + "-" + values.getDate() + "-" + values.getMonth() + 1);


So i get 2 different errors depending on how i type that string which (for me) makes me think that the error was in the code but perhaps its in the actual document or some compatability issues since im swedish and running some formats in google sheets like date in swedish version which is YYYY-MM-DD.
I have no idea if this could be causing issues but when i switch the code around it gives me different error messages.

But i guess this could also mean that somewhere in this process its trying to name something in this Date format and it cant do so and just leaves me with the first function it fails, either month or year in this case and perhaps thats not indicative of the code beeing wrong initself just that it cant replace or name the information as requested for some reason?


This is the entire script if that helps:

/*================================================= ================================================== =============*
Invoice Generator
================================================== ================================================== ============
Version: 1.0.0
Project Page: https://github.com/Sheetgo/invoice-generator
Copyright: (c) 2018 by Sheetgo

License: GNU General Public License, version 3 (GPL-3.0)
http://www.opensource.org/licenses/gpl-3.0.html
----------------------------------------------------------------------------------------------------------------
Changelog:

1.0.0 Initial release
*================================================= ================================================== =============*/

/**
* Project Settings
* @type {JSON}
*/
SETTINGS = {

// Spreadsheet Url (template filled with your data)
spreadsheetUrl: "<https://docs.google.com/spreadsheets/d/1NBEPoO_MchUlATvX5OXvIpCZ_alaKLawPja9Qgt_Cmw/edit?usp=sharing>",

// Spreadsheet name
sheetName: "Data",

// Document Url
documentUrl: "<https://docs.google.com/document/d/1mjs7bJ2dGRthi4CwyLEVF9GoSzoqZTkHDW28fNxU2m8/edit?usp=sharing>",

// Folder id
folderUrl: "https://drive.google.com/drive/folders/0B9O67uHE5cUJMUdqYVlnQ0NIQTg"

};


/**
* This funcion will run when you open the spreadsheet. It creates a Spreadsheet menu option to run the spript
*/
function onOpen() {
// Adds a custom menu to the spreadsheet.
SpreadsheetApp.getUi()
.createMenu('Invoice Generator')
.addItem('Generate Invoices', 'sendInvoice')
.addToUi();
}


/**
* Reads the spreadsheet data and creates the PDF invoice
*/
function sendInvoice() {
// Opens the spreadsheet and access the tab containing the data
var ss = SpreadsheetApp.openByUrl(SETTINGS.spreadsheetUrl);
Logger.log("apa", SETTINGS.folderUrl)
var dataSheet = ss.getSheetByName(SETTINGS.sheetName);

// Gets all values from the instanciated tab
var sheetValues = dataSheet.getDataRange().getValues();

// Gets the PDF url column index
var pdfIndex = sheetValues[0].indexOf("PDF Url");

// Gets the user's name (will be used as the PDF file name)
var clientNameIndex = sheetValues[0].indexOf("client_name");

var counter, invoiceNumCount, pdfInvoice, invoiceId, key, values, pdfName, invoiceNumber, invoiceDate;
for (var i = 1; i < sheetValues.length; i++) {

// Creates the Invoice
if (!sheetValues[i][pdfIndex]) {

// Duplicate teh template on Google Drive to manipulate the data
invoiceId = DriveApp.getFileById(SETTINGS.documentUrl.substrin g((SETTINGS.documentUrl.indexOf("/d/")+3), (SETTINGS.documentUrl.indexOf("/d/")+3+44))).makeCopy('Template Copy').getId();

// Instantiate the document
var docBody = DocumentApp.openById(invoiceId).getBody();

// Iterates over the spreadsheet columns to get the values used to write the document
for (var j = 0; j < sheetValues[i].length; j++) {

// Key and Values to be replaced
key = sheetValues[0][j];
values = sheetValues[i][j];

if (key === "date") {
// Invoice Date
invoiceDate = (values.getMonth() + 1) + "-" + values.getDate() + "-" + values.getFullYear();
replace('%date%', invoiceDate, docBody); // Write data



} else if (values) {
// Everything else appart from date values
if (key.indexOf("price") > -1 || key === "discount" || key.indexOf("total") > -1) {
replace('%' + key + '%', '' + values.toFixed(2), docBody); // Replace values
} else if (key === "tax_id") {
replace('%' + key + '%', "Org. nr. " + values, docBody); // Replace the tax_id
} else {
replace('%' + key + '%', values, docBody)
}

} else {
replace('%' + key + '%', '', docBody) // Replace empty string
}
}

// Get last invoice count from the tab 'Count'
counter = ss.getSheetByName('Count').getRange('A2');
invoiceNumCount = counter.getValue() + 1;

invoiceNumber = invoiceNumCount.padLeft(4, '0') + "-" + invoiceDate.split("-")[2];
replace('%number%', invoiceNumber, docBody);

// Rename the invoice document
pdfName = sheetValues[i][clientNameIndex] + " " + invoiceNumber;
DocumentApp.openById(invoiceId).setName(pdfName).s aveAndClose();

// Convert the Invoice Document into a PDF file
pdfInvoice = convertPDF(invoiceId);
// Set the PDF url into the spreadsheet
dataSheet.getRange(i + 1, pdfIndex + 1).setValue(pdfInvoice[0]);

// Update invoice counter
counter.setValue(invoiceNumCount);

// Delete the original document (will leave only the PDF)
Drive.Files.remove(invoiceId);
}
}
}


/**
* Convert a Google Docs into a PDF file
* @param {string} id - File Id
* @returns {*[]}
*/
function convertPDF(id) {
var doc = DocumentApp.openById(id);
var docBlob = doc.getAs('application/pdf');
docBlob.setName(doc.getName() + ".pdf"); // Add the PDF extension
var invFolder = SETTINGS.folderUrl.substring((SETTINGS.folderUrl.i ndexOf("/folders/")+9));
var file = DriveApp.getFolderById(invFolder).createFile(docBl ob);
var url = file.getUrl();
return [url, file.getId()];
}


/**
* Replace the document key/value
* @param {String} key - The document key to be replaced
* @param {String} text - The document text to be inserted
* @param {Body} body - the active document's Body.
* @returns {Element}
*/
function replace(key, text, body) {
return body.editAsText().replaceText(key, text);
}


/**
* Returns a new string that right-aligns the characters in this instance by padding them with any string on the left,
* for a specified total length.
* @param {Number} n - Number of characters to pad
* @param {String} str - The string to be padded
* @returns {string}
*/
Number.prototype.padLeft = function (n, str) {
return Array(n - String(this).length + 1).join(str || '0') + this;
};
OW - MY EYES!!! :help:

[Dude]

It's giving you 2 different errors because js will stop executing as soon as it throws an error. When it's year first, it errors that the year is the problem, when month is first it errors that the month is the problem.

If you want more help, you can dm me. Even people who LIKE js don't like js, normal people probably don't want to see it. I believe the data is the culprit.

[d_douglas]

Oh, this is just gross. Looking at code makes me kinda queasy.

[palincss]

Thing is the sheet give me the error MONTH message when i type it like this:

invoiceDate = (values.getMonth() + 1) + "-" + values.getDate() + "-" + values.getFullYear();
replace('%date%', invoiceDate, docBody);


And sheet give me the error YEAR message when i type it like this (only thing i changed in code):

invoiceDate = (values.getFullYear() + "-" + values.getDate() + "-" + values.getMonth() + 1);


So i get 2 different errors depending on how i type that string which (for me) makes me think that the error was in the code but perhaps its in the actual document or some compatability issues since im swedish and running some formats in google sheets like date in swedish version which is YYYY-MM-DD.
I have no idea if this could be causing issues but when i switch the code around it gives me different error messages.

Is it possible it's throwing an error on the first method it encounters? If you've got the year method first, it croaks on that; if you've got the month method first, that's where it croaks. Not inconsistent with the notion that the object itself has not been properly set up.

[redir]

kind of hard without seeing everything, but seems like your getMonth isn't defined or accessible from the class.

Run through a debugger like intellij and it'll probably help you (ie. highlight if the function isn't recognized etc.) Intellij should be available as a trial.

That's exactly what it is saying.

What is the object and where is it defined? Look in there for a function called getMonth and you will probably not find it. If that function is inherent in JS then you are probably missing that library.

Not a JS developer but I understand OOP.

[tuscanyswe]

Thanks everyone!

Dude i may take you up on that offer down the line, much appreciated!

For now i think its something i dont have enough time to dwell on as i have other more pressing matters atm. And the script works now.. Only on the last qued invoice do i get the error message, so it can function properly even with that fault as i can easily bypass it by just adding a bogus invoice in the end for the script to fail on. Not ideal but when i have more time to figure it out i shall revisit .)

Cheers
Björn