Open an excel sheet

now is ok?

Screenshot 2024-01-28 164440

This does it on my phone:

uses: Turni_New.xlsx - Google Sheets in the DataUri block

User may need to have Google Drive and/or Google Sheets installed on their device

I can't edit, can you help me edit my app

Crs_Group.aia (1.3 MB)

Timai2 we can edit like this maybe it's easier

Just apply the blocks i have shown to your app ....

If you want to use a google apps script then you will need something like this:

HI TIMAI2,I CAN'T UNDERSTAND IT GIVES ME AN ERROR WHEN I SEND TO CHANGE A PERSON'S TURN.
CAN YOU HELP ME UNDERSTAND IN MY FILE.AIA WHERE I AM WRONG

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);
  }
  
  // append row to end write row values
  else if ( fn == 'appendRow' ) {
    var data = JSON.parse('[' + e.parameter.DATA + ']');
    var lr = sh.getLastRow();
    sh.insertRowAfter(lr);
    rowNum = lr + 1;
    sh.getRange(rowNum,1,1,data[0].length).setValues(data);
    return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' appended');
  }
  
  // append column to end and write column values
  else if ( fn == 'appendCol' ) {
    var data = JSON.parse(e.parameter.DATA);
    var lc = sh.getLastColumn();
    sh.insertColumnAfter(lc);
    colNum = lc + 1;
    sh.getRange(1,colNum,data.length,1).setValues(data);
    return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' appended');
  }
  
  // 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 moved this to your existing topic because the apps script bears absolutely no relation to the CRUD example I linked to above.

There is also no need to SHOUT (typing in CAPITAL LETTERS here is considered rude)

If you are getting an error, then you need to post the full error here, then perhaps we can help. You should also indicate which part of the script you are calling, and show your blocks. YOu have already shared your spreadsheet.

1 Like

ok sorry, I didn't know about the capitalization, this my block in attachement

if I want to search by name and I do send
or if I change a shift and do send I get the error in attachemrnt



The error appears to be "pagina non travota" - page not found....

I don't understand how we can verify I can give you the.aia file, please can you help me? thank

TIMAI2 Can you help me understand where the error is?

There is something wrong with your script deployment.

HELLO TIMAI2, THIS is the script if I run it below I get an error

function doGet(e) {

  var ss = SpreadsheetApp.openById(e.parameter.sheetID);
  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);
  }
  
  // append row to end write row values
  else if ( fn == 'appendRow' ) {
    var data = JSON.parse('[' + e.parameter.DATA + ']');
    var lr = sh.getLastRow();
    sh.insertRowAfter(lr);
    rowNum = lr + 1;
    sh.getRange(rowNum,1,1,data[0].length).setValues(data);
    return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' appended');
  }
  
  // append column to end and write column values
  else if ( fn == 'appendCol' ) {
    var data = JSON.parse(e.parameter.DATA);
    var lc = sh.getLastColumn();
    sh.insertColumnAfter(lc);
    colNum = lc + 1;
    sh.getRange(1,colNum,data.length,1).setValues(data);
    return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' appended');
  }
  
  // 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);
  }
}

strong text Log esecuzione

15:14:46

Notifica

Esecuzione avviata

15:14:46

Errore

TypeError: Cannot read properties of undefined (reading 'parameter')

doGet

@ Code.gs:3

If the above is line 3 in your script, then it seems your sheetID is incorrect or missing ?

This is the sheetID for the spreadsheet from above link that you wanted to edit:
17O4PkI-U0vdjEytijh24Q-lg9fsMWj87
with a sheetname of Turni e Reperibilità H24 - 2023

This is the sheetID in your app (for the GSAI2 extension:
16UpesE3a8ym6ANgGzXXQZGuCZMg5AY5TrNXf8uvO_yg
with a sheetname of Foglio1

This is the sheetID it's wrong , this my doc


I have just tested the extension with your spreadsheet using my script and it works OK.

Something wrong with your script deployment

thanks, did you change it for me?

which script should I put in place of the current one