New Row in Spreadsheet for Every User

I feel like I’m over-analyzing this. The concept seems pretty straight forward and I understand how it works, but I don’t understand the extra reset column and the extra options column. I removed both and it still works. I’m also not following the relevance of your mention of 0. I get that 0 * 0 is 0 but how does that come into play with the extra reset column. Especially since I removed it with no effect. Maybe as a developer I’m thinking too hard and looking for something that’s already clear to me. Anyway. Nice work.

2 Likes

Tx for the feedback.

Some of the columns would come into play when creating a single reset button but not necessary as other ways exist to exploit weaknesses of the 0 field. 0 is always key for me because all number fields are based on the base 0. Even though we don’t see it, all number fields return 0. We replace it by entering other digits. But as an empty cell it’s 0. They only way you can manipulate a number cell to return a blank screen (not 0) is by disabling the 0 completely. I wouldn’t call it cheating or hacking the number field, but to be more precise “disabling” 0 completely by overriding it with something that does not belong to the numbers family. Best is always text given 0/1 conundrum (bits). As for the 00, it’s true I didn’t make any use of it but it’s a fool proof way of checking things. If you check my lawcap intro app you will notice i don’t clear the percentage fields by disabling the number field with text. I in fact deployed 0 to achieve the same end. So 0 is always key. It’s a bit more complicated than that but hopefully I will be able to show you and all our community members with working examples of the power of 00 in due course.

The only reason I was able to make this look simple and straightforward I would say was becz of my understanding of the function of 0 in number fields.

Tx and btw I found your concepts app really helpful, and plan to put the concepts to good use.

2 Likes

Setting a trigger to work onChange works fine when data is submitted from Glide guys the api. I use it to copy some data from one sheet to another, so I know it works and don’t see a reason why it couldn’t be used to create a reset function with a switch, checkbox, or choice component. There would be a bit of a delay though, because the changed value would have to be updated on the sheet, the trigger activated to run the script and clear all fields (including the one that activated the trigger), then the update data returned to Glide. Most likely wouldn’t take more than a few seconds, but wouldn’t be instant like @Wiz.Wazeer’s example. onEdit will not work because as you kind of indicated, the change is happening via the api instead of the directly in the sheet. onEdit only appears to work with edits made directly in the sheet.

@Wiz.Wazeer’s use of the choice component as a button is kind of brilliant. This allows for direct entry of a value to the sheet like a switch or checkbox, but it looks better. In some cases you can use Zapier to perform many functions that a script would, but a script would ultimately give you more control.

1 Like

no @Jeff_Hager a write action made by Glide doesn’t fire onEdit or onChange triggers in Google Sheet.

Unless you are using a undocumented parameter o trick (which I will want to see it :face_with_monocle:) none script will run under this scenario (API)

Try using this harmless code to test OnChange(e) event:

function OnChange(e)
{
Browser.msgBox(“Something changed!!”);
SpreadsheetApp.getActiveSpreadsheet().toast(e.changeType)
}

Even, if you create manually a new row, change a cell color o erase any cell, my script will run fine but instead, if you use Glide’s Data Editor to create a new basic column in Google Sheet you will see how your new column is created perfectly in 2-3 sec but the above script never ran.

Let me know your results to compare and know a little more about this.

Thanks in advance
Saludos

1 Like

You have to create a trigger for the script. Then it will work. In the trigger you specify which function you want to call. You set up the trigger using the highlighted icon in the attached image. Not by naming your function onChange. You can name the function whatever you want and the trigger will call it.

image

I see your reply and I am felling like a high school student! :rofl:

I know how to set up a trigger and associate it to a script my friend, don’t worry about it but anyway, here you can see my settings (in spanish… sorry!)

image

but did you carry out the test? Were you are able to run the script (mine or any yours) successfully with an OnChange() event?

Thanks again
Gavp

I haven’t tried your specific example, but I’ve been doing this with my app for the past year and it works fine. A record can be added from the app and it automatically fills data in other sheets for me. I don’t have the sheet open and the trigger works fine. Your script might be different because you are trying to show a toast message in the browser window instead of performing some other action.

Here is an old post with the script I’m using. It runs with the onChange Trigger;

These are the trigger settings I’m using:

@Jeff_Hager I finally discovered my big bug!!

Let me finish some homeworks I have and come with my fuc… reset button and explanation of my failures

Thanks for your time!

Feliz fin de semana

2 Likes

Hi again friends,

Here I modified my demo APP and included a Reset switch to clear the input values at the same time via script

You will see some lag to clear values but it is caused by Google Sheet’s script and/or your internet speed.

I hope it can be useful to anyone.

Saludos
Gavp

2 Likes

:grinning: Fantastic!!! Send me a copy of the onChange code if you would. You can drop it in my email if not here. Let me know.

Tx

The script is in sheet @Wiz.Wazeer … I think anyone can see/copy when the App is copied as template … or not? :lying_face:

Let me know if this doesn’t happen to paste it here

Saludos

:nerd_face:oops. Let me download and get back to you bro.

Tx

Okay got it. But for some weird reason not firing. Been at 5 minutes almost. Shouldn’t take that long. Also without access to your email no chance of viewing script. You’ve my email. Send me a screenshot if you could pls. Tx

I think you would have to reset the trigger under your own email account. You need to authorize the trigger to your own google account for it to work.

Jeff I still can’t get his switch to work; not his script, which I have yet to see. His button just freezes on me. I think Gvalero is working on it. Have you tested his reset function? Is it working on your side of the Atlantic ?

Here my code @Wiz.Wazeer

function ResetOnChange(e)
{
 var spreadsheet = SpreadsheetApp.getActive();
 var Row= spreadsheet.getCurrentCell().getRow();
 var Colum= spreadsheet.getCurrentCell().getColumn();
 var CellButtom= spreadsheet.getRange('E'+Row).getValue();
  
  //   SpreadsheetApp.getActiveSpreadsheet().toast(e.changeType) 
 if (e.changeType === 'EDIT' && Colum ==5 && CellButtom == true) 
 {
   spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Calculos'), true);  
   //  Browser.msgBox("La celda que cambio fue... " + 'E'+Row + " ... valor: "+ CellButtom);  

   var RangeToDel= "C"+ Row + ":" + "D" + Row
   spreadsheet.getRange('E'+Row).setValue(false);
   //  Browser.msgBox("Rango a Borrar: " + RangeToDel); 
   spreadsheet.getRange(RangeToDel).activate();
   spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true}); 
 }
}

As Jeef wrote above, you will need to authorize the trigger to your own google account to fire this script. At the 1st time, Google will ask your authorization for running this.

Saludos

2 Likes

Tx bro. Just saved me hours trying to sort it. But, for some weird reason I can’t get the reset button on your calc to fire. I have to remove the numbers manually. Pls have a look at your end again.

I’ll test it too with your code. See if we can get yours to work (the documented version), I will not need to resort to tricks.

Tx

Our User Profiles feature is released now. Video coming in a week or two.

5 Likes

Yeah man

I’m not seeing the template, just the app. So I’m not clear as to how you make the reset work without a script.