Read all and delete data in google sheet using script

[Shawn_Tan]

I have refer to youtube about how to read all and delete data in google sheet using the script code but when I press the button ReadAll, it show error message: Cannot parse text argument to "list from csv table" as a CSV-formatted table. Hope anyone could help me with this.

My script URL that I insert at the global url is
https://script.google.com/d/1dxryvS2EhqdLfsrw-EKtdYg6sSpIqYPDaQOgDktECYPU9F59trqgaXzV/edit?usp=sharing

and my google spreedsheet ID is
1ywhNjaM3d84PvQGgg_WIxDNsxavzaqw2F36B6Ur6pwI

[TIMAI2]

• Tidy up your script, use this:
  [note openByID changed to openById]

function doGet(e) {
  var ss = SpreadsheetApp.openById('1ywhNjaM3d84PvQGgg_WIxDNsxavzaqw2F36B6Ur6pwI');
  var sh = ss.getSheetByName('Failure');
  var message = '';

  //READ ALL RECORDS
  if ( e.parameter.func == "ReadAll") {
    var rg = sh.getDataRange().getValues(); 
    var outString = '';
      for(var row=0 ; row<rg.length ; ++row){
        outString += rg[row].join(',') + '\n';  
      }
    message = outstring; 
  }
    
   //DELETE SINGLE RECORD
   else if (e.parameter.func == "Delete") {
    var record = e.parameter.id;
    sh.deleteRow(parseInt(record) + 1);  
    message = 'Row Deleted';
 } 
  return ContentService.createTextOutput(message);
}

remember to create a new version

• Edit your blocks
  You do not need to set SpreadSheetID or Sheet because these are set in the script

• You might want to manually delete the empty rows in your spreadsheet

[Shawn_Tan]

Dear TIMAI2,

Thanks for the help, but when I try to run and debug the script, it shows error : TypeError: SpreadsheetApp.openByID is not a function (line 2, file "Code").

I have try to change the block to execute the global url but the error still the same which is : Cannot parse text argument to "list from csv table" as a CSV-formatted table.

[TIMAI2]

Should be openById a lowercase d at the end.
Sorry, my typo.

[Arturo]

HI,

I have the same Issue and I try to do the same but It do not work

could you help me please

I will show my codes

[Arturo]

[Arturo]

[TIMAI2]

Try setting the variables ss and sh either outside the doGet(e) function, at the top of the script, or at the very least before you call the ManageSheet(e) return. If you use the doPost(e) function, then you need to ensure that these variables are set.

Do not forget to re-deploy your script to a new version

[Arturo]

Thanks for the answer, but ist not still working, do I need to have an activator in app scrip, to start with the process ?

another question

to be able to modify the spreadsheet do I need to use a code like this?

because when I runn this code, In the app appears a message saying that the app is not able to read the code because of the format csv.

the way to runn the function was introducing the this URL

https://docs.google.com/spreadsheets/d/1CrkHxMFe2XtsKLhjHkQsK2279HED1M-79ys4yIEIj6I/export?format=csv

My doubt is if beacause the URL link with the end export?format=csv

it is not posible to modify the spreadsheet ?

thanks for your attention

[TIMAI2]

@Arturo

do I need to have an activator in app scrip, to start with the process ?

Not sure what you mean by this? You appear to have your script deployed correctly because you have a script url. Have you allowed the script to run as "Anyone" ?

@Arturo

to be able to modify the spreadsheet do I need to use a code like this?

No, because you have hardcoded the spreadsheet ID and the sheetName in the script

If you use this url:

@Arturo

https://docs.google.com/spreadsheets/d/1CrkHxMFe2XtsKLhjHkQsK2279HED1M-79ys4yIEIj6I/export?format=csv

You do not need to use the script (if your spreadsheet has access for anyone permissions). This will return the data in csv format, which you can convert to an AI2 list in the Web1.GotText.

This looks wrong

Because you are using doGet(e) to get data, you should be able to test your urls in your computer browser, which will either display results or download a csv file to your computer

[Arturo]

TIMA12 , Thanks for your answer,

The activator like this in app script

let me show you the screen where I allowed Anyone to have acces.

when I use in my computer it donwload this

but it is not letting me delead the row en the spreadsheet when I press the bottom liberar.

[TIMAI2]

You do not need any triggers. By sending a GET or POST from the app, the web app will run.

Test your two GET urls in your browser, or share your aia project here for my attention.

[Arturo]

I send you my aia proyect

I hope you can help please

[TIMAI2]

I will take a look soon...

[TIMAI2]

You have errors in your web app script (aside from the errors in your blocks), I will need to do more work on it later.

I also note your data upload blocks using a google form, does this work ???

[Arturo]

sorry at the beggining it works, but now, that I need to do more things like modify the spreadsheet and delete a row it is not working

If you can help me please how it need to be donne I will be really thanksfull for ever with you

what I need is to make registers in an spreadhsheet and then do be able to modify those registers my app inventor

[TIMAI2]

I am going to suggest that you try out my GSConnected extension

This will handle data uploads to the sheet, and data downloads, along with updates and deletions

Follow the instructions carefully and you should have a fully working Create/Read/Update/Delete setup for your app.

This will be much better than trying to work with a poorly put together script method by "others...."

[Arturo]

Thanks Deare TIMAI2,

You are really awesome, I use your codes:

METRIC RAT AI2 - Google Sheet CRUDQ II

and those work good, I will studie them to understand them in order to be able to make my own codes, really thanks.

have a nice day.

[Arturo]

TIMA

I have been working on the same code but I can't achieve the result that I'm looking for.

when I want to see the info in a Listview from a spreadsheet It shows me this message

Do you know that can I do, because with this selection I need to do an Update. from anther list pick.
I will show the app screen

And the app Inventor blocks

I wish you can show me the solution

[TIMAI2]

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…

use the above for sheet, script and blocks

Ran a quick test on your data, and it works OK, although it looks like there is some work to do on date formats. Obviously requires your layout/textboxes etc. to fit the data.