Google script update method

Teguh_Sampurna · July 16, 2021, 12:44pm

Can i ask some question… i have android apps that can save datadatabase at google spreadsheet,and the data table its gonna be like this…

and i have script update like this :

//Update if (e.parameter.func == “Update”) {
var sku = e.parameter.SKU;
var exp = e.parameter.EXPIRED;
var loc = e.parameter.LOCATION;
var stat = e.parameter.STATUS;

var lr= sh.getLastRow();
for(var i=1;i<=lr;i++){
var data_sku = sh.getRange(i, 1).getValue();
if(data_sku==sku){
sh.getRange(i, 2).setValue(exp);
sh.getRange(i, 3).setValue(loc);
sh.getRange(i, 4).setValue(stat);
return ContentService.createTextOutput(“Successfully Updated”).setMimeType(ContentService.MimeType.TEXT);

}
}
}

but if I want to change the status of data number 3 in the application, the only thing that changes is the first row.its gonna be like this

i think i should give 3 parameter variable besides sku, but i don’t know where to put it…

when i put code like this,its gonna be error…maybe anyone can help me //Update if (e.parameter.func == “Update”) {
var sku = e.parameter.SKU;
var exp = e.parameter.EXPIRED;
var loc = e.parameter.LOCATION;
var stat = e.parameter.STATUS;

var lr= sh.getLastRow();
for(var i=1;i<=lr;i++){
var data_sku = sh.getRange(i, 1).getValue();
var data_exp = sh.getRange(i, 2).getValue();
var data_loc = sh.getRange(i, 3).getValue();
if(data_sku= =sku and data_exp= =exp and data_loc==loc){
sh.getRange(i, 2).setValue(exp);
sh.getRange(i, 3).setValue(loc);
sh.getRange(i, 4).setValue(stat);
return ContentService.createTextOutput(“Successfully Updated”).setMimeType(ContentService.MimeType.TEXT);

}
}
}

maybe someone help me please

Learning.code123 · July 16, 2021, 1:03pm

have you known how to use javascript?

Still-learning · July 16, 2021, 1:05pm

Can you show us what you have tried via blocks? Are you getting error while sending from app or while saving script code in code editor?

Learning.code123 · July 16, 2021, 1:11pm

I think your foul in your first javascript code is you are using “return” too early
try putting “return” code after iteration finishes

Teguh_Sampurna · July 16, 2021, 1:30pm

no i have not…im just copy method from internet.but in his case the data in first column is unique and forbide to add same code,so in his code update method

for(var i=1;i<=lr;i++){
var data_sku = sh.getRange(i, 1).getValue();
if(data_sku==sku){
sh.getRange(i, 2).setValue(exp);
sh.getRange(i, 3).setValue(loc);
sh.getRange(i, 4).setValue(stat);
return ContentService.createTextOutput(“Successfully Updated”).setMimeType(ContentService.MimeType.TEXT);

}
}
}

how posibly i add some var because in my case, i have more than 1 same code at first column?

Learning.code123 · July 16, 2021, 1:31pm

do this first

Teguh_Sampurna · July 16, 2021, 1:35pm

function doGet(e) {
return ceksheet(e);
}

function doPost(e) {
return ceksheet(e);
}

function ceksheet(e){
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName(e.parameter.SH);

//CREATE
if (e.parameter.func == “Create”) {
var sku = e.parameter.SKU;
var exp = e.parameter.EXPIRED;
var loc = e.parameter.LOCATION;
var stat = e.parameter.STATUS;
var data=false;
var lr= sh.getLastRow();
for(var i=1;i<=lr;i++){
var data_plu = sh.getRange(i, 1).getValue();
if(data_sku==sku){
data=true;
}
}
var rowData = sh.appendRow([sku,exp,loc,stat]);
var result=“SUccessfully Added”;

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

}

//Read
if (e.parameter.func == “Read”) {
var rg=sh.getDataRange().getValues();
var data="";
for(var row=1;row<rg.length;++row){
data +=rg[row].join(’,’)+’\n’;
}
return ContentService.createTextOutput(data).setMimeType(ContentService.MimeType.TEXT);
}

//Update
for(var i=1;i<=lr;i++){
var data_sku = sh.getRange(i, 1).getValue();
if(data_sku==sku){
sh.getRange(i, 2).setValue(exp);
sh.getRange(i, 3).setValue(loc);
sh.getRange(i, 4).setValue(stat);
return ContentService.createTextOutput(“Successfully Updated”).setMimeType(ContentService.MimeType.TEXT);

}
}
}

//Delete
if (e.parameter.func == “Delete”) {
var sku = e.parameter.SKU;
var lr= sh.getLastRow();
for(var i=1;i<=lr;i++){
var data_sku = sh.getRange(i, 1).getValue();
if(data_sku==sku){
sh.deleteRow(i);
var rg=sh.getDataRange().getValues();
var data="";
for(var row=1;row<rg.length;++row){
data +=rg[row].join(’,’)+’\n’;
}
return ContentService.createTextOutput(data).setMimeType(ContentService.MimeType.TEXT);
break;
}
}
}

}

its my google script code at google sheet

Learning.code123 · July 16, 2021, 1:37pm

just try and learning it… enjoy it

Still-learning · July 16, 2021, 1:37pm

Three times you are posting the code without answering/trying properly. Pls avoid it. Try to follow the suggestion given above. If not show the error of your every attempt

Teguh_Sampurna · July 16, 2021, 1:54pm

i so sorry for that, my english so bad,and i dont know hot to explain it…but i dont have any error in my code,the error code show t the app after i click submit

error means “the script is complete, but it returns nothing”

Teguh_Sampurna · July 16, 2021, 1:56pm

i try my best but yeah i think i have any idea with disscus

Still-learning · July 16, 2021, 1:57pm

Can you in blocks mode? I hope you are committing mistake over there

And the common problems is how you are taking the row number only. If you wanna update selected cell or row , you need to use row index

Teguh_Sampurna · July 16, 2021, 2:00pm

how about share screen aia?its to much block

Still-learning · July 16, 2021, 2:00pm

If you have no probelm mean, pls carry on

Teguh_Sampurna · July 16, 2021, 2:10pm

i really appriciate it

Still-learning · July 16, 2021, 2:11pm

Are you Sharing the aia at here or in pm?

sugarlesscreator · July 16, 2021, 2:16pm

Don’t use sku for var i because item sku 1, 2 and 3 are the same. Use unique item for id ex: date or location

Teguh_Sampurna · July 16, 2021, 2:18pm

FOOD_MultiTools(2).aia (2.2 MB)
sorry

Teguh_Sampurna · July 16, 2021, 2:22pm

oke i think you know what i mean…so its possible to give 3 var or params at the same time?like if sku=sku,and exp= expiered and loc=location then just execute that data…or it imposibe?i mean var i must just select one between expire or location?

sugarlesscreator · July 16, 2021, 2:36pm

why not try?

You can try another way
simple

read data
select the item to edit
Find out the index
update by index
done