Data read modify Googlesheet Sheet3

Tomy_Tom · September 16, 2022, 4:55am

Hello everybody! I have big problem I made app send data to googlesheet and i want edit in this app. I try twice, but all time end error I have googlesheet with 5 spreadsheet. I want read data from Sheet3 and modify. Can anybody give idea very easy way to read and modify datas from googlesheet with 10 col and lot of row

I made it by this Video from youtube

https://www.youtube.com/watch?v=EC6ZOYVrdSg&t=0s

error screenshot
https://drive.google.com/file/d/1accSijpLtCojiPH8LdPd4G0kaUQ3OO4V/view?usp=sharingaia https://drive.google.com/file/d/1WW8wg-e2kbutuCvHSsYZc--PhwXouDju/view?usp=sharing

aia (test)
https://drive.google.com/file/d/1WW8wg-e2kbutuCvHSsYZc--PhwXouDju/view?usp=sharing

OLVAS btn = ReadAll TOROL btn = Delete MODOSIT btn = Modify

myxls (test) https://docs.google.com/spreadsheets/d/1rvwlyXLgQo0tsP1GnYPQxglvSd9f1FNj4MSkqR5mPxg/edit#gid=0

myscript (test)
https://script.google.com/home/projects/1K_qh0G79Dq-gSciZ-4DSZBcCjFh4uF-8U8cLgD8ttE6Ql_I4hFvzZ8Dc/edit

This picture in the post is real table Thanks a lot your help!

TIMAI2 · September 16, 2022, 6:03am

Hooray, another Hey Kal video follower ! This continues to throw up problems for developers...

Your spreadsheet image is unreadable, but you appear to have mixed images and text and multiple lines, which may be causing a problem. You may need to create another "working sheet" with clean data.

The script sets the sheet inside the spreadsheet to the first sheet. You need to modify the script to point to the 3rd sheet (ss.getSheets()[2]) - or move your "Sheet3" to be the first sheet in the spreadsheet.

Tomy_Tom · September 16, 2022, 6:14pm

Hello, thanks your answear
Im from Hungary i not speak English very well.
I try more and i have error message on if i use Sheet1 too.

Its my script:

function doGet(e) {

return ManageSheet(e);

}

function doPost(e) {

return ManageSheet(e);

}

function ManageSheet(e) {

//READ ALL RECORDS

if ( e.parameter.func == "ReadAll") {

var ss = SpreadsheetApp.getActive();

var sh = ss.getSheets()[0]; 

var rg = sh.getDataRange().getValues(); 

var outString = '';

  for(var row=0 ; row<rg.length ; ++row){

    outString += rg[row].join(',') + '\n';  

  } 

return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);

}

//DELETE SINGLE RECORD

else if (e.parameter.func == "Delete") {

var record = e.parameter.SZAM;

var ss = SpreadsheetApp.getActive();

var sh = ss.getSheets()[0];

sh.deleteRow(parseInt(record) + 1);  

return ContentService.createTextOutput("Success");

}

//READ SINGLE RECORD

else if ( e.parameter.func == "ReadRecord") {

var ss = SpreadsheetApp.getActive();

var sh = ss.getSheets()[0];

var rg = sh.getDataRange().getValues();

var outString = '';

outString += rg[parseInt(e.parameter.SZAM)].join(',');

return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);

}

//UPDATE SINGLE RECORD

else if (e.parameter.func == "Update") {

var ss = SpreadsheetApp.getActive();

var sh = ss.getSheets()[0];

var data = [ [ e.parameter.ELSO, e.parameter.MASODIK, e.parameter.NEM, e.parameter.ORSZAG, e.parameter.KOR ] ]; 

sh.getRange("B"+(parseInt(e.parameter.id)+1)+":F"+(parseInt(e.parameter.id)+1)).setValues(data); 

return ContentService.createTextOutput("Success");

}

//CREATE NEW RECORD

if (e.parameter.func == "Create") {

var ss = SpreadsheetApp.getActive();



var sh = ss.getSheets()[0];



var data =[e.parameter.SZAM, e.parameter.ELSO, e.parameter.MASODIK, e.parameter.NEM, e.parameter.ORSZAG, e.parameter.KOR]; 



sh.appendRow(data);

return ContentService.createTextOutput("Success");

}

TIMAI2 · September 16, 2022, 7:14pm

The video guide/script/blocks are not very well put together, and will only work in a certain configuration. I am working on a solution for this as a replacement.

It would help if you set your response content to a label, then you could see what is being returned.

My guess is that you have not deployed your apps script correctly. The script should be executed as you (your google account) and be accessible to "Anyone", not "Anyone with a google account" or just you.

Tomy_Tom · September 17, 2022, 10:48am

Its set anyone

TIMAI2 · September 17, 2022, 11:08am

again....

Tomy_Tom · September 17, 2022, 11:43am

Sorry Im Hungaryan
I understand You mean i change txtbox to label?

TIMAI2 · September 17, 2022, 11:49am

then you can see what is coming back, error or other issue with content

Tomy_Tom · September 18, 2022, 7:36am

Tomy_Tom · September 18, 2022, 7:51am

What is the problem with my code?

TIMAI2 · September 18, 2022, 9:47am

That script will only really work for the example data and app shown in the video.

Also, as previously advised, you probably need to clean your data on your sheet of all emoji and images

I now have a new script, that will work on most spreadsheets, and example app, but I need to write this up.

TIMAI2 · September 18, 2022, 11:06am

Now written up:

TIMAI2 · September 18, 2022, 7:42pm

I have moved all your subsequent posts here

Tomy_Tom · September 18, 2022, 4:16pm

Lot if thanks your help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

i try it and i have message when i click read all button:

Tomy_Tom · September 18, 2022, 4:32pm

I paste my sheet ID and sheetname is it okay? Or i not work good? Thanks your help!!!!!

TIMAI2 · September 18, 2022, 5:24pm

You did not need to edit the google apps script (This was the whole point of the exercise)
You set the sheetId (the id of the spreadsheet) and the sheetname (the specific sheet in the spreadsheet) in the variables in the app