I want to send a list of numbers from kodular app to google spreadsheet

[ehshan_khan]

I want to read a .csv file and send it to the google spreadsheet. I am following the following link.

METRIC RAT AI2 - Export CSV data (multiple data) to Google Sheet

But I am getting the following error

My app script code is as follows:

function doPost(e) {

var data = JSON.parse(e.postData.contents) ; // or >> eval(e.postData.contents) ;

var ss = SpreadsheetApp.getActive();

var sh = ss.getSheetByName(‘Sheet1’);

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

sh.appendRow(data[i]);

}

return ContentService.createTextOutput(“Success”) ;

}
And my kodular blocks are as follows

I am not understanding if I am doing anything wrong.

[TimAi2 (metricrat)]

What does global data look like, that you are sending to .Post Text?

[ehshan_khan]

I have attached the image of my testdata.csv. Thats the data in global data.

[TimAi2 (metricrat)]

Not sure where you problem is, it is working OK for me.

• You do not need the blocks to remove the first item in the list as you do not have a header row.

• Check the single quotes around Sheet1 in your script, make sure they are the straight down ones that you would get when typing in the script editor, not the ones copied from a community post, which may make them curly ones

• I get this if I put global data to a label:

[["2","2","1","1"],["2","2","1","1"],["2","2","1","1"],["2","2","1","1"],["2","2","1","1"]....]

Is this the structure you see ?

Make sure that you have deployed your script correctly after any changes, and check that the script url presented by the script matches the script url in your blocks

[ehshan_khan]

@TimAi2

[["2","2","1","1"],["2","2","1","1"],["2","2","1","1"],["2","2","1","1"],["2","2","1","1"]....]

I am getting
((2 2 1 1) (2 2 1 1) (2 2 1 1) (2 2 1 1)…)

Yes, I have changed the inverted commas around Sheet1 but the error persists.

[ehshan_khan]

I am getting error at line 3
JSON.parse(e.postData.contents)

[TimAi2 (metricrat)]

You need to go into the designer and tick the box for ShowListsAsJson. (Screen properties in AI2)

JSON.parse is expecting a JSON list as a string, and is not getting one. It needs the square brackets.

[dora_paz (Dora)]

@TimAi2

You need to go into the designer and tick the box for ShowListsAsJson . (Screen properties in AI2)

In Kodular you have to go to Project Settings - General and scroll down till you see Show Lists As Json

[ehshan_khan]

Hey this worked. Thanks a ton.
One more question. I have one ore question, I have a button. when I click it sends the data and append in the row. But when I click the button again, it appends the data is same column.

I want to append data to a new column every time I click the button. Any suggestions on that?

[TimAi2 (metricrat)]

That is a different setup altogether!

Are you sure you want it to do this:

[ehshan_khan]

Yes every time I click a button, I should start filling the data from the next empty column.

[TimAi2 (metricrat)]

OK, it is more complicated, I will have to do a bit of thinking…

[ehshan_khan]

Thank you for all the help.

I have done it myself. I might need more help as I am making something that is complex and very new to me.

Thanks again. I really appreciate it.

[TimAi2 (metricrat)]

Just for the record, given you did not share your solution…:

function doPost(e) {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Sheet1');
  var nc = sh.getLastColumn() + 1;
  var rows = JSON.parse(e.postData.contents);
  var irl = rows[0].length;
  sh.insertColumns(nc,irl);
  for (var i=0;i<rows.length;i++) {
    var range = sh.getRange(i+1,nc, 1, irl);
    range.setValues([rows[i]]);
  }
  return ContentService.createTextOutput("Success") ;
}

[ehshan_khan]

Thank you once again. This is my solution.

function doPost(e) {

var data = JSON.parse(e.postData.contents); // or >> eval(e.postData.contents) ;

var ss = SpreadsheetApp.getActive();

var sh = ss.getSheetByName(‘Sheet1’);

var col_number=sh.getLastColumn();

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

sh.getRange(1+i,col_number+1,1).setValue(data[i]);

}

return ContentService.createTextOutput(“Success”);

}

[TimAi2 (metricrat)]

Your script only inserts the 1st item of each set, this may be because you reach the end of the columns. You at least need to insert columns as well, or use my script to post the entire range.