Open an excel sheet

[TIMAI2]

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

[STEFANO_CHERUBINI]

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

[STEFANO_CHERUBINI]

Crs_Group.aia (1.3 MB)

[STEFANO_CHERUBINI]

@TIMAI2

Applicazione Web Script di Google Apps

Timai2 we can edit like this maybe it's easier

[TIMAI2]

@STEFANO_CHERUBINI

I can't edit

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:

Simple CRUD with Google Sheets and Apps Script (fixes Hay Kel flaws...)

I have lost count of the number of times the community has received requests for help from developers, after following the Hay Kel series of videos on CRUD with google sheets. The videos are well produced, and offer the viewer a good insight to creating the app-sheet-script ensemble required for CRUD activities. However, the script is flawed: a bound to sheet script, with code that only allows the bound spreadsheet and use of the first sheet; hard-coded columns and hard-coded row data for upda…

[STEFANO_CHERUBINI]

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);
  }
}

[TIMAI2]

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.

[STEFANO_CHERUBINI]

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

[TIMAI2]

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

[STEFANO_CHERUBINI]

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

[STEFANO_CHERUBINI]

TIMAI2 Can you help me understand where the error is?

[TIMAI2]

There is something wrong with your script deployment.

[STEFANO_CHERUBINI]

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

[TIMAI2]

@STEFANO_CHERUBINI

var ss = SpreadsheetApp.openById(e.parameter.sheetID);

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

[STEFANO_CHERUBINI]

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

Turni

[STEFANO_CHERUBINI]

[TIMAI2]

@TIMAI2

16UpesE3a8ym6ANgGzXXQZGuCZMg5AY5TrNXf8uvO_yg

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

Something wrong with your script deployment

[STEFANO_CHERUBINI]

thanks, did you change it for me?

[STEFANO_CHERUBINI]

which script should I put in place of the current one

[Spicy_Topics]

pls replace the script code with this scripts from the link below and deploy once again with option Anyone

METRIC RAT AI2 - Google Sheets for AppInventor

As said by tim, it is working fine, if i use above scripts and run.

see