Yes! I think I explained in the instructions somewhere. You have to make a separate project in google script because the name of the scripts are the same. As long as they both point to the same spreadsheet, you can add and read. I just have a @URLwriteScript and @URLreadScript variable for accessing from any piston.
Unfortunately, I did not figure out how to overwrite entire rows but I did create a script to replace individual values. You have to specify row, column, and value [in addition to tab if not default] and you can only do one value per call. [I tried multiple but for some reason, the parameters did not go to google script in the same order I listed them-bug?] I have a @URLreplaceScript for accessing this one.
Here is the script:
// version 2.0 - Guxdude
function doGet(request) {
if(request != null) {
var spreadSheet = SpreadsheetApp.openById("YOUR ID HERE");
// default tab name
var sheet = spreadSheet.getSheetByName("Testing");
var firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
var sheetHeaders=firstRowRange.getValues();
// No default column name. If none provided, entire sheet will be returned
var columnName = "";
var colFound=false;
var columnNumber = -1;
var rowNumber = -1;
//var firstDate=-1;
//var lastDate=-1;
//var firstRow=0;
//var lastRow=0;
var range;
var newValue;
var selectRow=1;
var selectCol=1;
var nRows=1;
var nCols=1;
//var tt=request.queryString;
Logger.log(request.parameters)
// Evaluate all the query parameters
for (var i in request.parameters) {
var paramName = i.toString();
var paramValue = request.parameter[i];
switch(paramName) {
case "XtabX":
// select specified tab by name and get new first row data
Logger.log("tab name is %s",paramValue);
sheet = spreadSheet.getSheetByName(paramValue);
firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
sheetHeaders=firstRowRange.getValues();
break;
case "colName":
// If colName parameter is found, search for that column
columnName = paramValue.toString().toLowerCase();
break;
case "colNum":
columnNumber=Number(paramValue);
Logger.log("row is %d", rowNumber);
break;
case "rowNum":
rowNumber=Number(paramValue);
Logger.log("row is %d", rowNumber);
break;
case "Value":
newValue=paramValue;
Logger.log("value is %s", newValue);
break;
default:
// Unrecognized parameter: ignore
}
}
if (columnNumber<0) {
// Parameters do not seem to be in order so have to evaluate all before setting
Logger.log("column name is %s", columnName);
colFound=false;
for(var x=0;x<sheetHeaders[0].length;x++) {
Logger.log(sheetHeaders[0][x].toString().toLowerCase())
if(columnName==sheetHeaders[0][x].toString().toLowerCase()) {
columnNumber=x+1;
colFound=true;
break;
}
}
if (colFound==false) {columnNumber=-1; Logger.log("column not found"); }
}
if (rowNumber>0 && columnNumber>0) {
// set value
sheet.getRange(rowNumber, columnNumber).setValue(newValue);
Logger.log("Setting value %s", newValue);
//range = sheet.getRange(selectRow,selectCol,nRows,nCols);
}
// get data values from specified range
// var data = range.getValues();
//return data in JSON format
//return sheet.getRange(rowNumber, columnNumber).getValue().toString();
return ContentService.createTextOutput(JSON.stringify(sheet.getRange(rowNumber, columnNumber).getValue())).setMimeType(ContentService.MimeType.JSON);
}
}
