-1

I am using IFTTT to push forms sent to my email address to a Google Sheet. The contents get pasted into column A in one big clump, and I can't figure out how to split the text into columns in a way that formats well. Ideally, I'd like to only include the answers to questions on the form in the columns to the right of column A.

Here is a sample of what gets pasted into column A:

New customer message on May  9, 2017 at 12:15 PM

You received a new message from your Online Store's contact form.

Name:

Jon Snow

Email:

sample@gmail.com

Q1:

Yes

Q2:

No

Q3:

yes

Q4:

no

Q5:

no

Is there some sort of script I could use to display the name, email address, and answers to the 5 questions in the 7 columns to the right of column A?

ReyAnthonyRenacia
  • 17,219
  • 5
  • 37
  • 56
Isaac4579
  • 1
  • 1

1 Answers1

0

I have a rudimentary solution that you can build on if you like.

My solution assumes a few things, firstly the name is always going to be two words, secondly the answers are always going to be yes or no.

This will work for any number of rows in column A.

The whole solution is based on splitting up the string in a particular way, and it's not very fluid as it assumes the amount of spaces and the formatting will always be the same. It's a start :)

    function myFunction() {
  var ss = SpreadsheetApp.getActiveSheet();
  var dataRangelength = ss.getDataRange().getLastRow();  
  var strRef = ss.getRange("A1:A" + dataRangelength);
  var str = strRef.getValues();

  for (var i = 0; i < dataRangelength; i++) {
    var row = str[i];
    var A1ref = i + 1
    var rowRef = ss.getRange("A" + A1ref);
    if (row != "") {

  var arr = row.toString().split(" ");
  var arr1 = arr[20].toString().split("\n");
  var arr2 = arr[21].toString().split("\n");

   rowRef.offset(0, 1).setValue(arr1[4] + " " + arr2[0]);
   rowRef.offset(0, 2).setValue(arr2[4]);
   rowRef.offset(0, 3).setValue(arr2[8]);
   rowRef.offset(0, 4).setValue(arr2[12]);
   rowRef.offset(0, 5).setValue(arr2[16]);
   rowRef.offset(0, 6).setValue(arr2[20]);
   rowRef.offset(0, 7).setValue(arr2[24]);
    }}
}
David
  • 131
  • 8