I have been struggling to insert the following formula into a single column in Google Sheets using JavaScript, but it keeps appearing in different columns due to the presence of commas.
=IF(A2="VALID", B2, 0)
Currently, I am utilizing the code provided below to create the CSV:
var CSVRowDemo = [];
var CSVDemo = 'data:text/csv;charset=utf-8,';
CSVRowDemo.push(['VALID/INVALID', 'Value', 'Check'])
let index = 2;
let check = '"' + '=IF(A'+index+'=\"VALID\"", B'+index+', 0)'.replace(/"/g, '""') + '"';
CSVRowDemo.push(['VALID', '100', check])
CSVRowDemo.forEach(function(rowArray) {
let row = rowArray.join(',');
CSVDemo += row + '\r\n';
});
encodedUri = encodeURI(CSVDemo);
link = document.createElement("a");
link.setAttribute("href", encodedUri);
link.setAttribute("download", "Basic.csv");
document.body.appendChild(link);
link.click();
My expected output resembles this (as seen in LibreOffice Calc): https://i.sstatic.net/TgkqC.png
However, in Google Sheets, it appears like this: https://i.sstatic.net/d0RU0.png
The closest solution I found is in this answer.
I am striving to achieve this solely with JavaScript, without relying on NodeJS or other libraries.
Your assistance is greatly valued.