function doGet(e) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var fn = e.parameter.FN;
var rg = sh.getDataRange().getValues();
// enter row number and column number to return A1 Notation Cell reference
if ( fn == 'getCellRef' ) {
var ref = sh.getRange(e.parameter.ROW,e.parameter.COL).getA1Notation();
return ContentService.createTextOutput(ref);
}
// enter start and end row numbers and column numbers to return A1 Notation Range reference
else if ( fn == 'getRangeRef' ) {
var ref = sh.getRange(e.parameter.ROW,e.parameter.COL,e.parameter.ROW1,e.parameter.COL1).getA1Notation();
return ContentService.createTextOutput(ref);
}
// enter A1 notation reference to return cell value
else if ( fn == 'readCell' ) {
var ref = sh.getRange(e.parameter.REF).getValue();
return ContentService.createTextOutput(ref);
}
// enter row number to return all values in row as a list
else if ( fn == 'readRow' ) {
var ref = sh.getRange(parseInt(e.parameter.ROW),1,1,rg[0].length).getValues();
return ContentService.createTextOutput(JSON.stringify(ref));
}
// enter column number (A=1/B=2/etc.) to return all values in column as a list
else if ( fn == 'readCol' ) {
var ref = sh.getRange(2,parseInt(e.parameter.COL),rg.length-1,1).getValues();
return ContentService.createTextOutput(JSON.stringify(ref));
}
// enter A1 notation reference to return range values as a list
else if ( fn == 'readRange' ) {
var ref = sh.getRange(e.parameter.REF).getValues();
return ContentService.createTextOutput(JSON.stringify(ref));
}
// returns all values on sheet, including headers
else if ( fn == 'readSheet' ) {
return ContentService.createTextOutput(JSON.stringify(rg));
}
// outputs results for SQL query of all data
else if ( fn == 'querySheet' ) {
var sql = e.parameter.SQL;
var hdr = e.parameter.HDR;
if ( hdr == 0 ) {
var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation().replace('A1','A2');
var qry = '=query(' + rgq + ';\"' + sql + '\";0)';
} else if ( hdr == 1 ) {
var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var qry = '=query(' + rgq + ';\"' + sql + '\";1)';
}
var ts = ss.insertSheet();
var setQuery = ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();
ss.deleteSheet(ts);
return ContentService.createTextOutput(JSON.stringify(getResult));
}
// enter row number to delete that row
else if ( fn == 'deleteRow' ) {
sh.deleteRow(parseInt(e.parameter.ROW));
return ContentService.createTextOutput('Row ' + e.parameter.ROW + ' has been deleted');
}
// enter row number to delete that row
else if ( fn == 'deleteCol' ) {
sh.deleteColumn(parseInt(e.parameter.COL));
return ContentService.createTextOutput('Column ' + e.parameter.COL + ' has been deleted');
}
// enter A1 notation reference to write cell value
else if ( fn == 'writeCell' ) {
sh.getRange(e.parameter.REF).setValue(e.parameter.DATA);
return ContentService.createTextOutput('Value: ' + e.parameter.DATA + ' written to cell ' + e.parameter.REF);
}
// enter row number to write row values
else if ( fn == 'writeRow' ) {
var data = JSON.parse('[' + e.parameter.DATA + ']');
sh.getRange(e.parameter.ROW,1,1,data[0].length).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to row ' + e.parameter.ROW);
}
// enter row number to write row values
else if ( fn == 'writeCol' ) {
var data = JSON.parse(e.parameter.DATA);
sh.getRange(1,e.parameter.COL,data.length,1).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to column ' + e.parameter.COL);
}
// enter A1 notation reference to write cell values
else if ( fn == 'writeRange' ) {
var data = JSON.parse(e.parameter.DATA);
var ref = sh.getRange(e.parameter.REF).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to cells ' + e.parameter.REF);
}
// enter row number to write row values
else if ( fn == 'addRow' ) {
var data = JSON.parse('[' + e.parameter.DATA + ']');
var lr = sh.getLastRow()+1;
sh.getRange(lr,1,1,data[0].length).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to last row ' + lr);
}
// enter row number to write row values
else if ( fn == 'addCol' ) {
var data = JSON.parse(e.parameter.DATA);
var lc = sh.getLastColumn()+1;
sh.getRange(1,lc,data.length,1).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to column ' + lc);
}
// enter A1 notation reference and formula to set formula to cell
else if ( fn == 'insertFormula' ) {
var ref = sh.getRange(e.parameter.REF).setValue(e.parameter.DATA);
return ContentService.createTextOutput('Formula: ' + e.parameter.DATA + ' inserted to ' + e.parameter.REF);
}
}
i must edit any thing on this script if i use anther sheet or no editing ?
and this id also must edit any thing on it if i change shhet or script
plsease make adjustment like this… also , I repeat, Pls see the aia
I forget to add one more make a list at here… it must be two make a list else it will throw error
dont use if else block else your other operations may not work, that is bakground changing…
Before that, test my aia… and see the response(if so, you wont have any doubt… else you will have few more doubts)
i tested your aia file it’s working good
then adjust in the same way… yours too will work…
WEB READ ALL DATA URL MUST BE LIKE THIS
https://docs.google.com/spreadsheets/d/1P-94GyGyVWosJ7NRStGxBtWczMeMlXkSECTELW6M3io/gviz/tq?tqx=out:csv&sheet=Sheet1&tq=SELECT A,C,F,G where H=‘Received’
OR THIS
-
It will read only the such COL when H contains the text received (kind of filtering)
-
It will read all, but you have to use certain if else block and all to take the above said process
it’s working now thanks tooo much but the problem with me is
in
id global variable
i must change highlighted blue from your sheet to my sheet
this your sheet url
https://docs.google.com/spreadsheets/d/1dNRdeferx0P7H4VJ7yt6oh8KMstyP19gwcroIIP3y8I/
dNRdeferx0P7H4VJ7yt6oh8KMstyP19gwcroIIP3y8I
i must put this in id variable between = and &
thanks for your support 
and also thanks @dora_paz thanks to much for your support
1 Like
So finally got working after 54 posts… Glad to hear that.
Only emoji mean , this is seems to be some kind of not following community guidelines.so Pls edit and add few comment… Keep making apps, any doubt arise don’t hesitate to serach first, and not satisifed mean create topic.
All the best.
1 Like
**
noted
**
and thanks for supporting
1 Like
if i want add condition to change col H to Received And Col I to Username
the Condition Is
If
Col I not Contain any Text
Then
change col H to Received And Col I to Username
I Must Make web To Read All Col I
And call Web Over Write I And H
Or any Thing else
And If I want Other Condition To review Data from sheet
https://docs.google.com/spreadsheets/d/1P-94GyGyVWosJ7NRStGxBtWczMeMlXkSECTELW6M3io/gviz/tq?tqx=out:csv&sheet=Sheet1&tq=SELECT A,C,F,G where H=‘Received’
gviz/tq?tqx=out:csv&sheet=Sheet1&tq=SELECT A,C,F,G
where H=‘Received’ & where I=‘Username
type Like This or other way
