[Solved] Need to store data in google spreadsheet

mate the thing is you can’t do formatting after getting spreadsheet CSV. So i have an alternative solution for this…

Use google app script

function doGet(e){
   var action = e.parameter.action;
   var ss=SpreadsheetApp.openByUrl("Your_Spreadsheet_URL");
   var sheet = ss.getSheetByName("Sheet_Name");
  
   if(action=="GC")
      return getthe_Column(sheet);
   if(action=="GR")
      var cn = e.parameter.cn;
      return getRow_data(cn,sheet);
  
}


function getthe_Column(sheet){
   var lr = sheet.getLastRow();
   var data = "ID";
   for(var i = 2; i<=lr ; i++){
      data += ","+sheet.getRange(i, 1).getValue();
   }
   //Logger.log(data);
   return ContentService.createTextOutput(data);
}

function getRow_data(cn,sheet){
   var lc = sheet.getLastColumn();
   var data ="";
   for(var i=1; i<=lc;i++){
       if(data!=""){
          data+= ","+sheet.getRange(cn, i).getValue();
       }else{
          data = sheet.getRange(cn, i).getValue();
       }
   }
   return ContentService.createTextOutput(data);
}

Blocks Screenshot

Sample Project : Get a copy

I have also included this sample projectct just in case you won’t understand the design and blocks side. Also if you have any qury feel free to ask.

:laughing: Bro i have sent you the googl app script code you have to deploy that! and then paste tat URL in the we-api

It is working bro thanks.
If any other comes I will ping you.

Thanks for your help. :slightly_smiling_face:

Okay…
Please mark :white_check_mark: SOLUTION

I am not able to scroll the column field even after enabling the scrollable option. What to do bro?

Bro instead of sending index value can we send item of the column and get the row, because in listviewer I will do some filters and get the data. I have tried it, can you tell me where I went wrong?

image

The script I have changed

function doGet(e){
var action = e.parameter.action;
var ss=SpreadsheetApp.openByUrl(“Novartis sheet - Google Sheets”);
var sheet = ss.getSheetByName(“Sheet1”);
if(action==“GC”)
return getthe_Column(sheet);
if(action==“GR”)
var cn = e.parameter.cn;
return getRow_data(cn,sheet);
}

function getthe_Column(sheet){
var lr = sheet.getLastRow();
var data = “ID”;
for(var i = 2; i<=lr ; i++){
if(sheet.getRange(i, 10).getValue()==“Pending”)
data += “,”+sheet.getRange(i, 1).getValue();
}
return ContentService.createTextOutput(data);
}

function getRow_data(cn,sheet){
var lc = sheet.getLastColumn();
var data ="";
var r;
for(var i=1;i<=lc;i++){
if(sheet.getRange(i,1).getValue()==cn){
r=i;
break;
}
}
for(var i=1; i<=lc;i++){
if(data!=""){
data+= “,”+sheet.getRange(r, i).getValue();
}else{
data = sheet.getRange(r, i).getValue();
}
}
return ContentService.createTextOutput(data);
}

the cn in the parameter is the row number not the column number.
If you want to get the row with any value instead of index you have to define a specific column name and then sersch this row[value/parameter] in that column and get the row no. from there you can retunrn the entire row in CSV format.

http://community.thunkable.com/t/need-to-store-data-in-google-spreadsheet/562987/29?u=sgirishdds

Is there any solution for this?

image

By the above blocks I am getting correct row value as output in text_input2.

image

But if I change the block I am getting listviewer values, why is this happening?

http://community.thunkable.com/t/need-to-store-data-in-google-spreadsheet/562987/34?u=sgirishdds

Bro is there anyway to solve this?

can you send the complete screenshot of your blocks? Are you using this G-var anywere else?

I have attached the screen shot and I am using GR in this place only.

set the response to text_input instead of using globle var, i’m still not getting the point of using that g-var there!! can you tell me the what eactly you are trying to do?

Bro I will show the particular column by filtering in listviewer, after selecting that particular row should be displayed.

If I give the response directly to text input, the output is correct, but if I give it to global variable the values are changing(it has listviewer values).

Here I am used the global variable to separate the values received and show in separate field.

It might also be worth considering using the Data Viewer component since it connects directly to Google Sheets:

2 Likes

Okay so all you are willing to do is call the row can then display the data of column in different different text inputs… right? if possible then send your project via PM

or use

1 Like

No bro I need to show the column in listviewer, after selecting I should show the selected row in different different text input.

How to send in PM?

I have provided a working sample on the forum which will read/write a Google Sheet, but I do not have time to support it. Did you search before posting?

@b.perkins

[Solved] Google oAuth using Web API - #10 by b.perkins

1 Like

You can refer to this post

1 Like

Hi, kindly share the link for the working sample to read/write to Google Sheet. Thank you.

hey @darrickloh you should know that the user’s post was 10 months ago and he / she was last seen at may 6 2020

I have provided an answer in this post
http://community.thunkable.com/t/working-with-google-sheet/1067002/9

1 Like