Converting Google Sheets (Apps Scripts) to a valid CSV format

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I have the below script which essentially looks at a google sheets tab, which has some data that needs to be uploaded to Google Import API (Google Analytics):

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('UK Only');

  var maxRows = ss.getLastRow();

  var maxColumns = ss.getLastColumn();

  var rows = ss.getRange(1, 1, maxRows, maxColumns).getValues();

  var rowsCSV = rows.join("n")



  var blobData = Utilities.newBlob(rowsCSV, "application/octet-stream", "GA import data");

  try {

    var upload = Analytics.Management.Uploads.uploadData("10131233", "UA-1234576-2", "righfghfghfgT8Dox1nwXDg", blobData);

    Logger.log("Test Data Import Successful");

  }

  catch (e) {

    console.error(e);

  }

The output is something like the below:

ga:productSku,ga:productName,ga:productBrand,ga:productCategoryHierarchy,ga:dimension25,ga:dimension28,ga:dimension31

456456456,example value wit,h char ,that "breaks" csv,fgjfgjf Tjghjghjg,FP,Women,dasdasd

456456456,example value wit,h char ,that "breaks" csv,123123123,FP,Women,dasdasd

456456456,example value wit,h char ,that "breaks" csv,Rdasdasd,FP,asdasdasd

The above shows some values that will break the CSV format (commas and quotes).

How do I go about properly formatting the CSV so that it will not break on these characters?

edited Nov 26 '18 at 17:51

TheMaster

10.6k3937

asked Nov 26 '18 at 17:35

Meisam Ebrahimi

468

Yes, open it in excel and other products, works as it should. But Google API parses the commas in rows as columns, so when there's a value that contains commas, it will parses that too.

– Meisam Ebrahimi
Nov 26 '18 at 18:38

2

Can you wrap it in quotes?

– XTOTHEL
Nov 26 '18 at 18:56

That's what it says in the docs to do, so every value should be wrapped in quotes (i.e. "val1","val2" etc.). The problem is, I couldn't figure out a way to do it per value, managed to do it per row (i.e."val1,val2") , but not per value.

– Meisam Ebrahimi
Nov 26 '18 at 19:07

so .getRange returns a Range, and Range.getValues returns a 2D array. You can DIY and map through the 2D array and wrap everything in quotes. Here's something that might help you, the function at line 44. gist.github.com/mrkrndvs/…

– XTOTHEL
Nov 27 '18 at 5:07

Use a chain of Array#map calls & Array#join to apply a function to each inner element and combine the results. This allows simple specification of the separator, the line ending, and the quote behavior (e.g. minimal, all, non-numeric).

– tehhowch
Nov 27 '18 at 10:59

add a comment |

I have the below script which essentially looks at a google sheets tab, which has some data that needs to be uploaded to Google Import API (Google Analytics):

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('UK Only');

  var maxRows = ss.getLastRow();

  var maxColumns = ss.getLastColumn();

  var rows = ss.getRange(1, 1, maxRows, maxColumns).getValues();

  var rowsCSV = rows.join("n")



  var blobData = Utilities.newBlob(rowsCSV, "application/octet-stream", "GA import data");

  try {

    var upload = Analytics.Management.Uploads.uploadData("10131233", "UA-1234576-2", "righfghfghfgT8Dox1nwXDg", blobData);

    Logger.log("Test Data Import Successful");

  }

  catch (e) {

    console.error(e);

  }

The output is something like the below:

ga:productSku,ga:productName,ga:productBrand,ga:productCategoryHierarchy,ga:dimension25,ga:dimension28,ga:dimension31

456456456,example value wit,h char ,that "breaks" csv,fgjfgjf Tjghjghjg,FP,Women,dasdasd

456456456,example value wit,h char ,that "breaks" csv,123123123,FP,Women,dasdasd

456456456,example value wit,h char ,that "breaks" csv,Rdasdasd,FP,asdasdasd

The above shows some values that will break the CSV format (commas and quotes).

How do I go about properly formatting the CSV so that it will not break on these characters?

edited Nov 26 '18 at 17:51

TheMaster

10.6k3937

asked Nov 26 '18 at 17:35

Meisam Ebrahimi

468

Yes, open it in excel and other products, works as it should. But Google API parses the commas in rows as columns, so when there's a value that contains commas, it will parses that too.

– Meisam Ebrahimi
Nov 26 '18 at 18:38

2

Can you wrap it in quotes?

– XTOTHEL
Nov 26 '18 at 18:56

That's what it says in the docs to do, so every value should be wrapped in quotes (i.e. "val1","val2" etc.). The problem is, I couldn't figure out a way to do it per value, managed to do it per row (i.e."val1,val2") , but not per value.

– Meisam Ebrahimi
Nov 26 '18 at 19:07

so .getRange returns a Range, and Range.getValues returns a 2D array. You can DIY and map through the 2D array and wrap everything in quotes. Here's something that might help you, the function at line 44. gist.github.com/mrkrndvs/…

– XTOTHEL
Nov 27 '18 at 5:07

Use a chain of Array#map calls & Array#join to apply a function to each inner element and combine the results. This allows simple specification of the separator, the line ending, and the quote behavior (e.g. minimal, all, non-numeric).

– tehhowch
Nov 27 '18 at 10:59

add a comment |

I have the below script which essentially looks at a google sheets tab, which has some data that needs to be uploaded to Google Import API (Google Analytics):

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('UK Only');

  var maxRows = ss.getLastRow();

  var maxColumns = ss.getLastColumn();

  var rows = ss.getRange(1, 1, maxRows, maxColumns).getValues();

  var rowsCSV = rows.join("n")



  var blobData = Utilities.newBlob(rowsCSV, "application/octet-stream", "GA import data");

  try {

    var upload = Analytics.Management.Uploads.uploadData("10131233", "UA-1234576-2", "righfghfghfgT8Dox1nwXDg", blobData);

    Logger.log("Test Data Import Successful");

  }

  catch (e) {

    console.error(e);

  }

The output is something like the below:

ga:productSku,ga:productName,ga:productBrand,ga:productCategoryHierarchy,ga:dimension25,ga:dimension28,ga:dimension31

456456456,example value wit,h char ,that "breaks" csv,fgjfgjf Tjghjghjg,FP,Women,dasdasd

456456456,example value wit,h char ,that "breaks" csv,123123123,FP,Women,dasdasd

456456456,example value wit,h char ,that "breaks" csv,Rdasdasd,FP,asdasdasd

The above shows some values that will break the CSV format (commas and quotes).

How do I go about properly formatting the CSV so that it will not break on these characters?

edited Nov 26 '18 at 17:51

TheMaster

10.6k3937

asked Nov 26 '18 at 17:35

Meisam Ebrahimi

468

I have the below script which essentially looks at a google sheets tab, which has some data that needs to be uploaded to Google Import API (Google Analytics):

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('UK Only');

  var maxRows = ss.getLastRow();

  var maxColumns = ss.getLastColumn();

  var rows = ss.getRange(1, 1, maxRows, maxColumns).getValues();

  var rowsCSV = rows.join("n")



  var blobData = Utilities.newBlob(rowsCSV, "application/octet-stream", "GA import data");

  try {

    var upload = Analytics.Management.Uploads.uploadData("10131233", "UA-1234576-2", "righfghfghfgT8Dox1nwXDg", blobData);

    Logger.log("Test Data Import Successful");

  }

  catch (e) {

    console.error(e);

  }

The output is something like the below:

ga:productSku,ga:productName,ga:productBrand,ga:productCategoryHierarchy,ga:dimension25,ga:dimension28,ga:dimension31

456456456,example value wit,h char ,that "breaks" csv,fgjfgjf Tjghjghjg,FP,Women,dasdasd

456456456,example value wit,h char ,that "breaks" csv,123123123,FP,Women,dasdasd

456456456,example value wit,h char ,that "breaks" csv,Rdasdasd,FP,asdasdasd

The above shows some values that will break the CSV format (commas and quotes).

How do I go about properly formatting the CSV so that it will not break on these characters?

javascript csv google-apps-script google-analytics google-sheets

edited Nov 26 '18 at 17:51

TheMaster

10.6k3937

asked Nov 26 '18 at 17:35

Meisam Ebrahimi

468

edited Nov 26 '18 at 17:51

TheMaster

10.6k3937

asked Nov 26 '18 at 17:35

Meisam Ebrahimi

468

edited Nov 26 '18 at 17:51

TheMaster

10.6k3937

edited Nov 26 '18 at 17:51

TheMaster

10.6k3937

edited Nov 26 '18 at 17:51

TheMaster

10.6k3937

asked Nov 26 '18 at 17:35

Meisam Ebrahimi

468

asked Nov 26 '18 at 17:35

Meisam Ebrahimi

468

asked Nov 26 '18 at 17:35

Meisam Ebrahimi

468

Yes, open it in excel and other products, works as it should. But Google API parses the commas in rows as columns, so when there's a value that contains commas, it will parses that too.

– Meisam Ebrahimi
Nov 26 '18 at 18:38

2

Can you wrap it in quotes?

– XTOTHEL
Nov 26 '18 at 18:56

That's what it says in the docs to do, so every value should be wrapped in quotes (i.e. "val1","val2" etc.). The problem is, I couldn't figure out a way to do it per value, managed to do it per row (i.e."val1,val2") , but not per value.

– Meisam Ebrahimi
Nov 26 '18 at 19:07

so .getRange returns a Range, and Range.getValues returns a 2D array. You can DIY and map through the 2D array and wrap everything in quotes. Here's something that might help you, the function at line 44. gist.github.com/mrkrndvs/…

– XTOTHEL
Nov 27 '18 at 5:07

Use a chain of Array#map calls & Array#join to apply a function to each inner element and combine the results. This allows simple specification of the separator, the line ending, and the quote behavior (e.g. minimal, all, non-numeric).

– tehhowch
Nov 27 '18 at 10:59

add a comment |

Yes, open it in excel and other products, works as it should. But Google API parses the commas in rows as columns, so when there's a value that contains commas, it will parses that too.

– Meisam Ebrahimi
Nov 26 '18 at 18:38

2

Can you wrap it in quotes?

– XTOTHEL
Nov 26 '18 at 18:56

That's what it says in the docs to do, so every value should be wrapped in quotes (i.e. "val1","val2" etc.). The problem is, I couldn't figure out a way to do it per value, managed to do it per row (i.e."val1,val2") , but not per value.

– Meisam Ebrahimi
Nov 26 '18 at 19:07

so .getRange returns a Range, and Range.getValues returns a 2D array. You can DIY and map through the 2D array and wrap everything in quotes. Here's something that might help you, the function at line 44. gist.github.com/mrkrndvs/…

– XTOTHEL
Nov 27 '18 at 5:07

Use a chain of Array#map calls & Array#join to apply a function to each inner element and combine the results. This allows simple specification of the separator, the line ending, and the quote behavior (e.g. minimal, all, non-numeric).

– tehhowch
Nov 27 '18 at 10:59

Yes, open it in excel and other products, works as it should. But Google API parses the commas in rows as columns, so when there's a value that contains commas, it will parses that too.

– Meisam Ebrahimi
Nov 26 '18 at 18:38

Can you wrap it in quotes?

– XTOTHEL
Nov 26 '18 at 18:56

That's what it says in the docs to do, so every value should be wrapped in quotes (i.e. "val1","val2" etc.). The problem is, I couldn't figure out a way to do it per value, managed to do it per row (i.e."val1,val2") , but not per value.

– Meisam Ebrahimi
Nov 26 '18 at 19:07

so .getRange returns a Range, and Range.getValues returns a 2D array. You can DIY and map through the 2D array and wrap everything in quotes. Here's something that might help you, the function at line 44. gist.github.com/mrkrndvs/…

– XTOTHEL
Nov 27 '18 at 5:07

Use a chain of Array#map calls & Array#join to apply a function to each inner element and combine the results. This allows simple specification of the separator, the line ending, and the quote behavior (e.g. minimal, all, non-numeric).

– tehhowch
Nov 27 '18 at 10:59

add a comment |

1 Answer
1

active

oldest

votes

Managed to resolve this with the below function, I had to account for quotes and commas within values that Google API doesn't like:

function arrayToCSV (twoDiArray) {

    var csvRows = ;

    for (var i = 0; i < twoDiArray.length; ++i) {

        for (var j = 0; j < twoDiArray[i].length; ++j) {

            twoDiArray[i][j] = '"' + twoDiArray[i][j].replace('"','""') + '"';

        }

        csvRows.push(twoDiArray[i].join(','));

    }



    return csvRows.join('rn');

};

Making sure to call it with the below:

  var maxRows = ss.getLastRow();

  var maxColumns = ss.getLastColumn();

  var rows = ss.getRange(1, 1, maxRows, maxColumns).getValues();

  var rowsCSV = arrayToCSV(rows);

answered Dec 19 '18 at 10:39

Meisam Ebrahimi

468

stackoverflow.com/help/someone-answers

– tehhowch
Dec 19 '18 at 13:59

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53486305%2fconverting-google-sheets-apps-scripts-to-a-valid-csv-format%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Managed to resolve this with the below function, I had to account for quotes and commas within values that Google API doesn't like:

function arrayToCSV (twoDiArray) {

    var csvRows = ;

    for (var i = 0; i < twoDiArray.length; ++i) {

        for (var j = 0; j < twoDiArray[i].length; ++j) {

            twoDiArray[i][j] = '"' + twoDiArray[i][j].replace('"','""') + '"';

        }

        csvRows.push(twoDiArray[i].join(','));

    }



    return csvRows.join('rn');

};

Making sure to call it with the below:

  var maxRows = ss.getLastRow();

  var maxColumns = ss.getLastColumn();

  var rows = ss.getRange(1, 1, maxRows, maxColumns).getValues();

  var rowsCSV = arrayToCSV(rows);

answered Dec 19 '18 at 10:39

Meisam Ebrahimi

468

stackoverflow.com/help/someone-answers

– tehhowch
Dec 19 '18 at 13:59

add a comment |

Managed to resolve this with the below function, I had to account for quotes and commas within values that Google API doesn't like:

function arrayToCSV (twoDiArray) {

    var csvRows = ;

    for (var i = 0; i < twoDiArray.length; ++i) {

        for (var j = 0; j < twoDiArray[i].length; ++j) {

            twoDiArray[i][j] = '"' + twoDiArray[i][j].replace('"','""') + '"';

        }

        csvRows.push(twoDiArray[i].join(','));

    }



    return csvRows.join('rn');

};

Making sure to call it with the below:

  var maxRows = ss.getLastRow();

  var maxColumns = ss.getLastColumn();

  var rows = ss.getRange(1, 1, maxRows, maxColumns).getValues();

  var rowsCSV = arrayToCSV(rows);

answered Dec 19 '18 at 10:39

Meisam Ebrahimi

468

stackoverflow.com/help/someone-answers

– tehhowch
Dec 19 '18 at 13:59

add a comment |

Managed to resolve this with the below function, I had to account for quotes and commas within values that Google API doesn't like:

function arrayToCSV (twoDiArray) {

    var csvRows = ;

    for (var i = 0; i < twoDiArray.length; ++i) {

        for (var j = 0; j < twoDiArray[i].length; ++j) {

            twoDiArray[i][j] = '"' + twoDiArray[i][j].replace('"','""') + '"';

        }

        csvRows.push(twoDiArray[i].join(','));

    }



    return csvRows.join('rn');

};

Making sure to call it with the below:

  var maxRows = ss.getLastRow();

  var maxColumns = ss.getLastColumn();

  var rows = ss.getRange(1, 1, maxRows, maxColumns).getValues();

  var rowsCSV = arrayToCSV(rows);

answered Dec 19 '18 at 10:39

Meisam Ebrahimi

468

Managed to resolve this with the below function, I had to account for quotes and commas within values that Google API doesn't like:

function arrayToCSV (twoDiArray) {

    var csvRows = ;

    for (var i = 0; i < twoDiArray.length; ++i) {

        for (var j = 0; j < twoDiArray[i].length; ++j) {

            twoDiArray[i][j] = '"' + twoDiArray[i][j].replace('"','""') + '"';

        }

        csvRows.push(twoDiArray[i].join(','));

    }



    return csvRows.join('rn');

};

Making sure to call it with the below:

  var maxRows = ss.getLastRow();

  var maxColumns = ss.getLastColumn();

  var rows = ss.getRange(1, 1, maxRows, maxColumns).getValues();

  var rowsCSV = arrayToCSV(rows);

answered Dec 19 '18 at 10:39

Meisam Ebrahimi

468

answered Dec 19 '18 at 10:39

Meisam Ebrahimi

468

answered Dec 19 '18 at 10:39

Meisam Ebrahimi

468

answered Dec 19 '18 at 10:39

Meisam Ebrahimi

468

stackoverflow.com/help/someone-answers

– tehhowch
Dec 19 '18 at 13:59

add a comment |

stackoverflow.com/help/someone-answers

– tehhowch
Dec 19 '18 at 13:59

stackoverflow.com/help/someone-answers

– tehhowch
Dec 19 '18 at 13:59

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ytukyg