I am using the onedit function which in googl...

(Nick Parsons) #1

I am using the onedit function which in google sheets works perfectly. However im using appsheet to edit the tables in google sheets.

When I edit and change open to closed, the row should move to another sheet.

This works fine if i change the text in the table.

When using the form in appsheet it works in a way that the data changes, but the script onedit does not run.

Is there a better funstion or even a function at all to get sheets to understand that the value has changed?

(Nick Parsons) #2

@Levent_KULACOGLU Hi, I have just tried that also but that did not work.

My code is below.

Do I have to only change the top line onedit to onchange?

This is what i tried but with no luck.

function onEdit(){

myFunction1();

myFunction2(); } function myFunction1() {

// moves a row from a sheet to another when a magic value is entered in a column

// adjust the following variables to fit your needs

// see productforums.google.com - Google Groups

var sheetNameToWatch = “CLOSED”;

var columnNumberToWatch = 10; // column A = 1, B = 2, etc.

var valueToWatch = “Open”;

var sheetNameToMoveTheRowTo = “OPEN”;

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = SpreadsheetApp.getActiveSheet();

var range = sheet.getActiveCell();

if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {

var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);

var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);

sheet.deleteRow(range.getRow());

} } function myFunction2() {

// moves a row from a sheet to another when a magic value is entered in a column

// adjust the following variables to fit your needs

// see https://productforums.google.com/d/topic/docs/ehoCZjFPBao/discussion

var sheetNameToWatch = “OPEN”;

var columnNumberToWatch = 10; // column A = 1, B = 2, etc.

var valueToWatch = “Closed”;

var sheetNameToMoveTheRowTo = “CLOSED”;

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = SpreadsheetApp.getActiveSheet();

var range = sheet.getActiveCell();

if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {

var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);

var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);

sheet.deleteRow(range.getRow());

} } Google Groups productforums.google.com

(Levent KULAÇOĞLU) #3

@Nick_Parsons1

With AppSheet you shall use onChange(e) not onEdit(e) because; just like onOpen(e), it works only if you manually open the gSheet and/or manually edit it.

(Levent KULAÇOĞLU) #4

@Nick_Parsons1

I’m talking about Action Buttons in AppSheet. To experience them: 1.) Open your app in the editor 2.) Goto Behaviour >> Actions 3.) Choose Add New Action 4.) Action name i.e. Close Project 5.) For a record of this table choose your Open Projects table/slice 6.) If this condition is true set an expression which will show/hide this button in Detail UX of a record i.e. [STATUS]=“Open” 7.) Do this choose Data: set the value of a column 8.) Set this column choose your [STATUS] column 9.) To this value type CLOSED 10.) Choose an icon that you see fit 10.) Prominence set to Display prominently

Save and Verify…Now when you choose any open project’s record from your table/UX, you will notice the action button in the Detail UX.

With this, you or your user do not need to open/edit the record any more. Clicking this button will automatically record CLOSED value to the [STATUS] column and sync the app.

(Levent KULAÇOĞLU) #5

@Nick_Parsons1

it totally relies on how you control onChange(e) Event handler that I have explained a couple of posts above. So I can say that they are wrong.

(Nick Parsons) #6

@Levent_KULACOGLU Great, that still gives me hope…I will follow your post right now for the action button, hope I can achieve it!!! thak you, will let you know how i do…

(Levent KULAÇOĞLU) #7

@Nick_Parsons1 provided should you require further assistance, please drop me a line at levent@able3ventures.com, I will be glad to help.

(Levent KULAÇOĞLU) #8

@Nick_Parsons1 or share you app with me as a co-author and share your gSheet with edit access, I can do it for you…It not more than a half an hour work really

(Nick Parsons) #9

@Levent_KULACOGLU really? thank you…I have just sent the app and sheet hopefully

(Levent KULAÇOĞLU) #10

@Nick_Parsons1 your code shall be edited. This code cannot work with onChange(e)

(Nick Parsons) #11

Ah i see…any idea of what needs to be changed?

(Levent KULAÇOĞLU) #12

@Nick_Parsons1 you shall refer to e parameter inside your code. For example: Instead of: SpreadsheetApp You should be referring to: e.source Provided you visit our website at www.able3ventures.com you can read a couple of my blog posts there to have an idea able3ventures.com - ABLE3 VENTURES, LLC ABLE3 VENTURES, LLC able3ventures.com

(Nick Parsons) #13

@Levent_KULACOGLU Hi, thanks for the reply, please see this comment below explaining how I got on using onchange.

I could not seem to get it to work either.

I have two sheets, sheet names are “open” and “closed”.

In both sheets I have a column names status.

The status can be in either position, open or closed.

When the record is originally sent from my appsheet app the status is set to open.

The records all start off in sheet open.

As some tasks are complete the record, or row will need to be changed from open to close. When this happens the row moves automatically from sheet open to sheet closed.

I can repeat this for any row and it works perfect using a script with nedit function.

However, here is the problem…

Because I have a form that sends the data to google sheets or can edit google sheets from the form, the onedit does not activate.

The cell does change as it should from open to close or vice versa, but the function does not run as google does not detect that the cell has changed.

If I manually changet the value using the drop down option on the cell, perfect, the row moves.

Surely, this can be overcome…I need it to work so that users will be able to make changes to the google sheet from the app by selecting open or close.

I have also tried to use onchange, this does not work because when i change the cell, the move happens, but because the move happens and something changes again it all goes a bit messy…

(Nick Parsons) #14

I see your site is using appsheet

(Levent KULAÇOĞLU) #15

@Nick_Parsons1

With onChange(e) it’s important to control the cell change that you need to activate your main or sub function. When AppSheet records a new data to the gSheet, the change is always in a Range not a particular cell. I understood that your [STATUS] columns has an initial value of “OPEN” for all new records. Here the important point is, how you actually CLOSE a particular project. Provided you are opening that record, changing the [STATUS] value and saving it, the result will be the same than adding a new record: AppSheet will write/overwrite the whole record.

What I may advice; will be use of an Action Button to close the project which will record a “CLOSED” value to the [STATUS] column explicitly. This way or the other, you should always check the Column Index of the [STATUS] column for a change and then run your “move” script. You can achive this with something like i.e.:

function anyFunctionNameYouLike (e) {

var sSht = e.source;

var sht = e.source.getActiveSheet;

var shtName = sht.getName();

var index = 10 // [STATUS] column index

var row = sht.getRow();

var rangeWidth = sht.getActiveRange.getWidth();

if (shtName === " _YourSheetNameHere _" && (index >= 1 && index <= rangeWidth)) { // if [STATUS] column is inside the range width…

if (sht.getRange(row, index).getValue() === “CLOSED”) { // if [STATUS] value is CLOSED

myMoveFunction();

}

} }

Hope it helps…

(Nick Parsons) #16

@Levent_KULACOGLU Hi, I am very new to gsheet and scripts so its hard for me to understand properly. I have read through your message a couple of times and its starting piece together, I think…

Currently to change the status I go in the app, in the app I have two views, open and closed, each of them showing the relevant table data.

I then edit the record, switch from open to close and then sync. This is where I would then like google sheet to move the row.

I would like the user to be able to edit the records from the app.

Do you have any examples of n action button, do you mean a button on the sheet?

(Nick Parsons) #17

@Levent_KULACOGLU Someone who was looking at it for me said this:

“This won’t work that easy. This script will still trigger onChange event and it will run again. (Resulting in the wrong result). I have tried similar way too”.

Does this make sense or could they be wrong? I hope not, I really need this to work now…out of frustration if nothing else…it must work…