1

I have been trying to add a formula shown below to a single column in Google sheets using JavaScript, but it is resulting in different columns, due to the comma.

=IF(A2="VALID", B2, 0)

Currently, the code I am using to generate the CSV is below:

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();

Expected to see it like this. (This is how it is rendered in LibreOffice Calc.) Expected to see it like this.

Rather saw it like this in Google Sheets. Rather saw it like this.

The closest, which comes to answer this is from this answer.

I am trying to use only Javascript instead of NodeJS or other libraries.

Any help is appreciated.

remedcu
  • 526
  • 1
  • 10
  • 31

1 Answers1

2

Although I'm not sure about the method for putting the created CSV data to Google Spreadsheet, can you test the the following modification?

From:

let check = '"' + '=IF(A'+index+'=\"VALID\"", B'+index+', 0)'.replace(/"/g, '""') + '"';

To:

let check = `"=IF(A${index}=""VALID"", B${index}, 0)"`;

or

let check = '"=IF(A'+index+'=""VALID"", B'+index+', 0)"';
  • In my environment, I could confirm that the CSV data with the following modification can be imported to the Google Spreadsheet.
Tanaike
  • 181,128
  • 11
  • 97
  • 165