Google Sheets – CSV Download Comes Out on One Line

google sheets

I have created a very simple spreadsheet of 3 columns with about 5 rows of data (as a test). When I download it as a CSV it comes out all in one line; I would expect each row to be a separate line. How can I achieve this?

If I populate the first 3 columns and first 4 rows I would expect this (and get it from Excel) when opened in a text editor (e.g. Notepad):

a,b,c
hi,how,are
you,doing,today
i,like,kittens

Instead, I get this:

a,b,chi,how,areyou,doing,todayi,like,kittens

Best Answer

I just did a test myself, and can see what you're seeing.

The problem isn't with Google or the download. The problem is that Windows (and thus Notepad) expects to see both a carriage return character (ASCII 13) and a linefeed character (ASCII 10) to indicate the end of the line. The CSV generated by Google Docs only uses the linefeed character so, to Notepad, there's no indication where the line ends.

Excel is different and can recognize the linefeed as the end of the line, which is why it opens there just fine. Note, though, that if you use Excel for Windows to create a CSV file, it will use a carriage return and linefeed at the end of each line.

In order to see the CSV file in the way you expect, you need to use a text editor/viewer that's smarter about linefeeds without carriage returns. Notepad++ is the one I use, but there are a plethora of Notepad replacements out there.

Trigger

Depending on how you're going to identify the incoming message, you'll use one of the following triggers:

Any new email in inbox
New email in inbox from
New starred email in inbox
New email in inbox labeled
New email in inbox from search

"Subject" is one of the ingredients available, which you'll obviously need. There's other useful things, like "ReceivedAt".

Action

You'll want Add row to spreadsheet where you'll defined the name of the spreadsheet, what goes in the row, and the folder path in Google Drive where it's located.

There are several recipes already shared that might service. This recipe, for instance, saves a new row for every received message.

Google-sheets – Creating a function that runs when a Google spreadsheet is opened

This little script will:

create an extra menu item, thus making it accessible throughout the workbook. T
the menu item will set the active range to column E, last row + 1.

See the example I've prepared: Creating a function...Google spreadsheet is opened (edit rights)

function onOpen() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // create menu
  var menu = [{name: "get last row", functionName: "getLastRow"}];

  // add to menu
  ss.addMenu("Rows", menu);  

  // execute function
  getLastRow();  
}

function getLastRow() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActive();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get column F
  var indLastCol = sh.getLastColumn()-1;

  // get last row + 1
  var indRowWithout = sh.getLastRow()+1;

  // set new active range
  sh.setActiveCell('E'+indRowWithout);
}

Best Answer

Related Solutions

Google-sheets – How to extract information from the subject line and add to Google Sheets

Trigger

Action

Google-sheets – Creating a function that runs when a Google spreadsheet is opened

Related Topic