It looks like the insert_value() function for that script loops through every row to check if some Id already exists. It performs a getRange() call for every single row. This is known to be a slower operation.
The Solution
We need to modify that script so that it gets the whole column at once, then runs through that column, rather than getting a single row at a time...
This is just one example of the script doing this, but it looks like it happens many times...
var lr= sheet.getLastRow();
for(var i=1;i<=lr;i++){
var id1 = sheet.getRange(i, 2).getValue();
if(id1==id){
flag=0;
var result="Id already exist..";
} }
I've updated it to make it more efficient by getting the whole column at once:
var lr= sheet.getLastRow();
var ids = sheet.getRange(1, 2, lr, 1).getValues(); //Get the whole ids column at once
for(var i=1;i<=ids.length;i++){ // Now loop through the column to see if id already exists.
if(ids[i]==id){
flag=0;
var result="Id already exists..";
}
}
This is the whole updated script. I have not tested this code you should use it with test data and fully test all the features before implementing with live, important data!
function doGet(e){
var op = e.parameter.action;
var ss=SpreadsheetApp.openByUrl("Your Spread sheet URL");
var sheet = ss.getSheetByName("Sheet1");
if(op=="insert")
return insert_value(e,sheet);
//Make sure you are sending proper parameters
if(op=="read")
return read_value(e,ss);
if(op=="update")
return update_value(e,sheet);
if(op=="delete")
return delete_value(e,sheet);
}
//Recieve parameter and pass it to function to handle
function insert_value(request,sheet){
var id = request.parameter.id;
var country = request.parameter.name;
var flag=1;
var lr= sheet.getLastRow();
var ids = sheet.getRange(1, 2, lr, 1).getValues(); //Get the whole ids column at once
for(var i=1;i<=ids.length;i++){ // Now loop through the column to see if id already exists.
if(ids[i]==id){
flag=0;
var result="Id already exists..";
}
}
//add new row with recieved parameter from client
if(flag==1){
var d = new Date();
var currentTime = d.toLocaleString();
var rowData = sheet.appendRow([currentTime,id,country]);
var result="Insertion successful";
}
result = JSON.stringify({
"result": result
});
return ContentService
.createTextOutput(request.parameter.callback + "(" + result + ")")
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
function read_value(request,ss){
var output = ContentService.createTextOutput()
data = {};
//Note : here sheet is sheet name , don't get confuse with other operation
var sheet="Source";
data.records = readData_(ss, sheet);
var callback = request.parameters.callback;
if (callback === undefined) {
output.setContent(JSON.stringify(data));
} else {
output.setContent(callback + "(" + JSON.stringify(data) + ")");
}
output.setMimeType(ContentService.MimeType.JAVASCRIPT);
return output;
}
function readData_(ss, sheetname, properties) {
if (typeof properties == "undefined") {
properties = getHeaderRow_(ss, sheetname);
properties = properties.map(function(p) { return p.replace(/\s+/g, '_'); });
}
var rows = getDataRows_(ss, sheetname),
data = [];
for (var r = 0, l = rows.length; r < l; r++) {
var row = rows[r],
record = {};
for (var p in properties) {
record[properties[p]] = row[p];
}
data.push(record);
}
return data;
}
function getDataRows_(ss, sheetname) {
var sh = ss.getSheetByName(sheetname);
return sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
}
function getHeaderRow_(ss, sheetname) {
var sh = ss.getSheetByName(sheetname);
return sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
}
//update function
function update_value(request,sheet){
var output = ContentService.createTextOutput();
var id = request.parameter.id;
var flag=0;
var country = request.parameter.name;
var lr= sheet.getLastRow();
var ids = sheet.getRange(1, 2, lr, 1).getValues(); //Get the whole ids column at once
for(var i=1;i<=ids.length;i++){ // Now loop through the column to see if id already exists.
if(ids[i]==id){
sheet.getRange(i+1,3).setValue(country);
var result="value updated successfully";
flag=1;
}
}
if(flag==0)
var result="id not found";
result = JSON.stringify({
"result": result
});
return ContentService
.createTextOutput(request.parameter.callback + "(" + result + ")")
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
function delete_value(request,sheet){
var output = ContentService.createTextOutput();
var id = request.parameter.id;
var country = request.parameter.name;
var flag=0;
var lr= sheet.getLastRow();
var ids = sheet.getRange(1, 2, lr, 1).getValues();
for(var i=1;i<=ids.length;i++){
if(ids[i]==id){
sheet.deleteRow(i+1);
var result="value deleted successfully";
flag=1;
}
}
if(flag==0)
var result="id not found";
result = JSON.stringify({
"result": result
});
return ContentService
.createTextOutput(request.parameter.callback + "(" + result + ")")
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
