Google Sheets --> read/add/sort/delete/select

franck_PERRET · January 7, 2020, 7:56pm

J arrive a lire le fichier, et a ajouter des données dans le fichier.

Mais je n arrive pas et modifier trier et supprimer des données

Pouvez vous m’aider

function doGet(e) {
var ss = SpreadsheetApp.openByUrl(e.parameter.File);
var sheet = ss.getSheetByName(e.parameter.Sheet);

  ActionsFichier(e,sheet);
  
}
 
function doPost(e) { 
  var ss = SpreadsheetApp.openByUrl(e.parameter.File);
  var sheet = ss.getSheetByName(e.parameter.Sheet);

  ActionsFichier(e,sheet);

}
 
 
function ActionsFichier(e,sheet) {
  
  
  //lock sheet to prevent concurrent changes  
  var lock = LockService.getPublicLock();
  lock.waitLock(5000);  
  
  
    // 
  if (e.parameter.Action=="Delete") {
   var dataToDelete=e.parameter.DataToDelete; 
    sheet.deleteRows(parseInt(dataToDelete)+1);

  }
  
  // Add 
  if (e.parameter.Action=="Add") {
    var name = e.parameter.Name ;
    var age = e.parameter.Age ;
    
    sheet.appendRow([name,age]);
    
   return ContentService.createTextOutput("Success");
  
}
  
  // change one element
 else  if (e.parameter.Action=="Change") { 
    
    
  }
  
  
  lock.releaseLock();
}

WitreRead(1).aia (5.3 KB)

TIMAI2 · January 7, 2020, 9:16pm

Deleting A Record (ROW)

Read the documentation: https://developers.google.com/apps-script/reference/spreadsheet/sheet#deleteRow(Integer)

You will need a number not a name (TB_Name.txt is a name) - use the index of a list ?

Sorting Your Data

Again read the documentation: https://developers.google.com/apps-script/reference/spreadsheet/range#sortsortspecobj

Modifying the Data

This is more complicated, I will put together an example for you

TIMAI2 · January 7, 2020, 9:33pm

Modifying Data (not tested)

Let us assume your data looks like this:

ID Name Age

01 John 24
02 Jane 27
03 David 35
04 Sue 19

You will need a unique identifier for each row

Let us say you want to change row 02 to:

02 Raymond 45

To modify data in a row on a spreadsheet, the best way to do it is to

call in all the data from the sheet
find the row you want to change
make the changes

Save the data back to the spreadsheet

 function doPost(e) {

 var ss = SpreadsheetApp.getActive();
 var sh = ss.getSheetByName("Sheet1");
 var data = sh.getDataRange().getValues();
 var row = "";

 for (var i=0;i<data.length;i++) {
   if ( data[i][0] == e.parameter.id ) {
   row = i;
 }
 }

 var values = [[e.parameter.id,e.parameter.name,e.parameter.age)]];
 var range = sh.getRange("A" + [row+1] + ":C" +[row+1]);
 range.setValues(values);

 }

franck_PERRET · January 11, 2020, 8:07am

Merci pour votre répose

je ne comprends pas mais ça ne fonctionne toujours pas.

2 Problémes
1 les données ne changes pas dans la feuille de calcules
2 return ContentService.createTextOutput("Success"); ne retourne pas de message????

Merci pour votre aide

Franck

TIMAI2 · January 11, 2020, 9:50am

Several Issues…

Check that your selection index matches the row number in ID
Is your web app script bound to the spreadsheet ? If so you do not need to send spreadsheet and sheet information in your doPost (see Spreadsheet.getActive() in the script)
If you want to use the automatic ID numbering, then you need to remove all blank rows from the spreadsheet, delete any numbers entered, and not send any data to the sheet (e.parameter.id) when adding or updating an entry. The line in the script should look like this:
var values = [["",e.parameter.name,e.parameter.age]];

sheetviewupdate

The update full url should look the same:
https://script.google.com/macros/s/AKf.....CTRQ/exec?id=4&name=Sue&age=25
This is all you need to send, if just updating. (I am using GET not post so I can test in a browser)

Full script:
function doGet(e) {

 var ss = SpreadsheetApp.getActive();
 var sh = ss.getSheetByName("Sheet1");
 var data = sh.getDataRange().getValues();
 var row = "";

 for (var i=0;i<data.length;i++) {
   if ( data[i][0] == e.parameter.id ) {
   row = i;
 }
 }

 var values = [["",e.parameter.name,e.parameter.age]];
 var range = sh.getRange("A" + [row+1] + ":C" +[row+1]);
 range.setValues(values);
 
 return ContentService.createTextOutput("Success");

 }

I hope this helps…

franck_PERRET · January 11, 2020, 1:34pm

what do you meen by : Is your web app script bound to the spreadsheet ?

in do not know how to bound my app scirpt to the spreadsheet

franck_PERRET · January 11, 2020, 1:36pm

do you konw why : return ContentService.createTextOutput("Success");
do not retun "success"

TIMAI2 · January 11, 2020, 1:56pm

See here:

This is probably because you are using Spreadsheet.getActive() in a standalone script ?

franck_PERRET · January 11, 2020, 5:35pm

great scripte works now the problem was because my script was not bound

it is bound finally

but "return ContentService.createTextOutput(“Success”);" still does not work even if this time script is bound....

any idea?

TIMAI2 · January 11, 2020, 7:45pm

Diifficult to say. Would need “full” access to your spradsheet, script, blocks (aia) and script to understand what is going wrong and test

franck_PERRET · January 12, 2020, 6:26am

i found the probleme it is the diference between post and get

return ContentService.createTextOutput("Success");
 work whith get but not whith post

TIMAI2 · January 12, 2020, 9:34am

OK, well done

thumbsup2

You can use both, just have a doGet and a do Post in your script:

doGet() {
<code>;
return ContentService.createTextOutput("Success");
}

doPost() {
<code>;
return ContentService.createTextOutput("Success");
}

The only difference is that you don’t use the “?” before the first parameter for doPost in the posttext block

franck_PERRET · January 12, 2020, 10:14am

Merci beaucoup pour votre aide je progresse enfin ( a tout petit pas mais grâce a vous)

Encore 2 petits problèmes.

Add —> efface les numéros automatique
Read --> ne retourne rien???

function doGet(e) {

var ss = SpreadsheetApp.getActive();
//var sheet = ss.getSheetByName(“Names”);
var sheet = ss.getSheets()[0];

//lock sheet to prevent concurrent changes
var lock = LockService.getPublicLock();
lock.waitLock(5000);

//
if (e.parameter.Action==“Read”) {
var data = sheet.getDataRange().getValues();
var outString=’’;

for (var row=0;row<data.lenght;row++){   
     outString+=data[row].join(',')+'\n';     
}      
return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);

}

// Add
else if (e.parameter.Action==“Add”) {

var values = [["",e.parameter.name,e.parameter.age]];
sheet.appendRow(values);

return ContentService.createTextOutput("Add OK");

}

// edit one element
else if (e.parameter.Action==“Edit”) {

var data = sheet.getDataRange().getValues();
var row = “”;

for (var i=0;i<data.length;i++) {
if ( data[i][0] == e.parameter.numRowToEdit ) {
row = i;
}
}

var values = [["",e.parameter.name,e.parameter.age]];
var range = sheet.getRange(“A” + [row+1] + “:C” +[row+1]);
range.setValues(values);

return ContentService.createTextOutput(“Edit OK”);
}

//delete on rows
else if (e.parameter.Action==“Delete”) {

var NumRowToDelete=e.parameter.NumRowToDelete; 
sheet.deleteRow(NumRowToDelete);

return ContentService.createTextOutput("Delete OK");

}

lock.releaseLock();

}

GoogleSheet.aia (4.4 KB)

TIMAI2 · January 12, 2020, 10:26am

READ:

You have a spelling mistake in your code:
for (var row=0;row<data.**lenght**;row++){

change to
for (var row=0;row<data.length;row++){

For ADD
Please explain what you mean ? If using the automatic index/numbering on the sheet, then you do not need to send an index number in the doGet/doPost, the data should be appended to the bottom of the sheet and a new index will appear. You would need to call back the data to get the new index (or hopefully know this if your data on the app is already aligned with the data on the sheet…

franck_PERRET · January 12, 2020, 5:41pm

Before adding a new data

After addind a new data

i do have a ref! error no more numbers and my ligne never add

the only code is

var ss = SpreadsheetApp.getActive();
//var sheet = ss.getSheetByName(“Names”);
var sheet = ss.getSheets()[0];
var values = [["",e.parameter.name,e.parameter.age]];
sheet.appendRow(values);

Sorry for the last speling erro i whould have seen it.

TIMAI2 · January 12, 2020, 6:24pm

You have empty rows in your spreadsheet. Look down at the bottom row 1001 see what you have there. As I suggested remove all empty rows. Not sure why else this is happening, “” should just do nothing in a cell?

franck_PERRET · January 12, 2020, 6:50pm

ok you where true lol i had some thing some where…

is it possible to add more than 1000 row?

TIMAI2 · January 12, 2020, 7:14pm

No, you need to remove all the empty rows, then the appendRow will go in the right place

See like this:

sheetviewupdate

TIMAI2 · January 12, 2020, 7:27pm

A simple test works for me:

SCRIPT:

function doGet(e) {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName("Sheet1");
sh.appendRow(["",e.parameter.name,e.parameter.age]);
return ContentService.createTextOutput("Success");
}

url to send via browser or blocks:

https://script.google.com/macros/s/AKfyc...o67f/exec?name=Roger&age=47

Diggio3000 · March 24, 2020, 8:33am

Salve a tutti, vi faccio i miei complimenti per aver trovato il modo di collegare Google Sheet, volevo chiedervi un piacere, potete pubblicare l’intero codice script corretto da utilizzare?

grazie mille