Help with Script please, its driving me mad!

58 views
Skip to first unread message

ross whittome

unread,
Nov 5, 2019, 1:27:01 PM11/5/19
to google-apps-sc...@googlegroups.com
I have copied this script from another form I use and cannot get it to work!

Folder to my form and sheet can be found here:

On the 'DIGITAL RESPONSES' sheet, they are not pulling through to the 'QUEUE' sheet and then they should be able to move and be removed from each page depending on progress box

Can anyone help please?

function onSubmit(e){
  var targetSheet = SpreadsheetApp.getActive().getSheetByName('QUEUE');
  var values = e.values;
  values.unshift('','','');
  targetSheet.appendRow(values);
}
  
function onEdit(e) {
  var ss = e.source;
  var activatedSheetName = ss.getActiveSheet().getName();
  var activatedCell = ss.getActiveSelection();
  var activatedCellRow = activatedCell.getRow();
  var activatedCellColumn = activatedCell.getColumn();
  var activatedCellValue = activatedCell.getValue();
  
  var pendingExpenseReportSheet = ss.getSheetByName("QUEUE"); // source sheet
  var approvedExpenseReportSheet = ss.getSheetByName("COMPLETED"); // target sheet
  var printQueueReportSheet = ss.getSheetByName("CANCELLED");
  
  // if the value in column B is "Proof & approval", move the row to target sheet
  if (activatedSheetName == pendingExpenseReportSheet.getName() && activatedCellColumn == 2 && activatedCellValue == "COMPLETED") {
    // insert a new row at the second row of the target sheet
    // move the entire source row to the second row of target sheet
    var rangeToMove = pendingExpenseReportSheet.getRange(/*startRow*/ activatedCellRow, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ pendingExpenseReportSheet.getLastColumn()).getValues();
    approvedExpenseReportSheet.appendRow(rangeToMove[0]);
    // delete row from source sheet
    pendingExpenseReportSheet.deleteRow(activatedCellRow);
  }
  if (activatedSheetName == approvedExpenseReportSheet.getName() && activatedCellColumn == 2 && activatedCellValue == "CANCELLED") {
    // insert a new row at the second row of the target sheet
    // move the entire source row to the second row of target sheet
    var rangeToMove = approvedExpenseReportSheet.getRange(/*startRow*/ activatedCellRow, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ pendingExpenseReportSheet.getLastColumn()).getValues();
    printQueueReportSheet.appendRow(rangeToMove[0]);
    // delete row from source sheet
    approvedExpenseReportSheet.deleteRow(activatedCellRow);
  }
}


Cheers,

Ross

Trevor Scanlan

unread,
Nov 5, 2019, 5:12:04 PM11/5/19
to google-apps-sc...@googlegroups.com
My apologies. I can't run your script or try your form since I'm not allowed. I was just going to check and see if the variables contain the expected information by using Logger.log() but maybe you have already done this?

On Tue, Nov 5, 2019 at 12:27 PM 'ross whittome' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:
I have copied this script from another form I use and cannot get it to work!

Folder to my form and sheet can be found here:

On the 'DIGITAL RESPONSES' sheet, they are not pulling through to the 'QUEUE' sheet and then they should be able to move and be removed from each page depending on progress box


Can anyone help please?

Cheers,

Ross

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3cbd8650-b52d-4afd-8ff2-aba60bc99c9e%40googlegroups.com.


--
Trevor Scanlan
Technology Assistant
Shenandoah Community School District
304 West Nishna Road
Shenandoah, IA 51601

ross whittome

unread,
Nov 5, 2019, 5:20:25 PM11/5/19
to google-apps-sc...@googlegroups.com
No I haven’t tried this yet, I’m quite new to all of this! I think I’ve given access now ?

Thanks 

Ross

--
Ross Whittome
Dad - Graphic Designer - Cub Scout Leader - DCC South London Scouts
Be Bold or italic, never regular
11 College gardens | Tooting | SW17 7UF | 07568383904









Riël Notermans

unread,
Nov 5, 2019, 5:24:59 PM11/5/19
to Google Apps Script Community
Have you setup your trigger to run onSubmit when form submits?

Riël Notermans

Op woensdagen vrij • Zzapps B.V. • High Tech Campus 9, 5656 AE Eindhoven • T 040 711 41 94 • E ri...@zzapps.nl • www.zzapps.nl
WIJ MAKEN HET ONDENKBARE MOGELIJK


To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFQQ5asK-nn7DsYnpDAGWJu9Te3TU2bi5HSgzMoVSvXJGaPjLQ%40mail.gmail.com.

ross whittome

unread,
Nov 5, 2019, 5:25:49 PM11/5/19
to google-apps-sc...@googlegroups.com
Yes

Trevor Scanlan

unread,
Nov 6, 2019, 10:29:05 AM11/6/19
to google-apps-sc...@googlegroups.com
I'll check again today.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFQQ5asK-nn7DsYnpDAGWJu9Te3TU2bi5HSgzMoVSvXJGaPjLQ%40mail.gmail.com.

ross whittome

unread,
Nov 6, 2019, 11:01:05 AM11/6/19
to Google Apps Script Community
Thanks,

Basically  when the form is submitted the response goes to 'Digital Content Changes (responses)' sheet and should add to the 'QUEUE' sheet and from there can be assigned to someone and when progress is updated it moves to another sheet.

This all works correctly on my other sheet here: 

I tried to copy this to a new sheet and now not working?

Cheers,

Ross


On Wednesday, November 6, 2019 at 3:29:05 PM UTC, Trevor Scanlan wrote:
I'll check again today.

On Tue, Nov 5, 2019 at 4:20 PM 'ross whittome' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:
No I haven’t tried this yet, I’m quite new to all of this! I think I’ve given access now ?

Thanks 

Ross
On Tue, 5 Nov 2019 at 22:12, Trevor Scanlan <scan...@shenandoah.k12.ia.us> wrote:
My apologies. I can't run your script or try your form since I'm not allowed. I was just going to check and see if the variables contain the expected information by using Logger.log() but maybe you have already done this?

On Tue, Nov 5, 2019 at 12:27 PM 'ross whittome' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:
I have copied this script from another form I use and cannot get it to work!

Folder to my form and sheet can be found here:

On the 'DIGITAL RESPONSES' sheet, they are not pulling through to the 'QUEUE' sheet and then they should be able to move and be removed from each page depending on progress box


Can anyone help please?

Cheers,

Ross

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.


--

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.
--
Ross Whittome
Dad - Graphic Designer - Cub Scout Leader - DCC South London Scouts
Be Bold or italic, never regular
11 College gardens | Tooting | SW17 7UF | 07568383904

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

ross whittome

unread,
Nov 6, 2019, 12:10:15 PM11/6/19
to Google Apps Script Community
Trevor,

I have managed to fix the issue, thank you so much for looking at this for me. Have a great day!

Ross

On Wednesday, November 6, 2019 at 3:29:05 PM UTC, Trevor Scanlan wrote:
I'll check again today.

On Tue, Nov 5, 2019 at 4:20 PM 'ross whittome' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:
No I haven’t tried this yet, I’m quite new to all of this! I think I’ve given access now ?

Thanks 

Ross
On Tue, 5 Nov 2019 at 22:12, Trevor Scanlan <scan...@shenandoah.k12.ia.us> wrote:
My apologies. I can't run your script or try your form since I'm not allowed. I was just going to check and see if the variables contain the expected information by using Logger.log() but maybe you have already done this?

On Tue, Nov 5, 2019 at 12:27 PM 'ross whittome' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:
I have copied this script from another form I use and cannot get it to work!

Folder to my form and sheet can be found here:

On the 'DIGITAL RESPONSES' sheet, they are not pulling through to the 'QUEUE' sheet and then they should be able to move and be removed from each page depending on progress box


Can anyone help please?

Cheers,

Ross

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.


--

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.
--
Ross Whittome
Dad - Graphic Designer - Cub Scout Leader - DCC South London Scouts
Be Bold or italic, never regular
11 College gardens | Tooting | SW17 7UF | 07568383904

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

ross whittome

unread,
Nov 6, 2019, 12:16:52 PM11/6/19
to google-apps-sc...@googlegroups.com
Fixed it! thanks so much for taking the time to look at it for me

Cheers,
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CABPHLXpd%3D%2Bb1a3JMGdkLFE6gpCCARYUXKcUsMAXj1QFnoFcRXw%40mail.gmail.com.

Trevor Scanlan

unread,
Nov 6, 2019, 12:36:48 PM11/6/19
to google-apps-sc...@googlegroups.com
fantastic! Care to share what it was? remove onEdit and used just onSubmit? Make sure to remove any access that has been shared outside of your domain.

On Wed, Nov 6, 2019 at 11:10 AM 'ross whittome' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:
Trevor,

I have managed to fix the issue, thank you so much for looking at this for me. Have a great day!

Ross

On Wednesday, November 6, 2019 at 3:29:05 PM UTC, Trevor Scanlan wrote:
I'll check again today.

On Tue, Nov 5, 2019 at 4:20 PM 'ross whittome' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:
No I haven’t tried this yet, I’m quite new to all of this! I think I’ve given access now ?

Thanks 

Ross
On Tue, 5 Nov 2019 at 22:12, Trevor Scanlan <scan...@shenandoah.k12.ia.us> wrote:
My apologies. I can't run your script or try your form since I'm not allowed. I was just going to check and see if the variables contain the expected information by using Logger.log() but maybe you have already done this?

On Tue, Nov 5, 2019 at 12:27 PM 'ross whittome' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:
I have copied this script from another form I use and cannot get it to work!

Folder to my form and sheet can be found here:

On the 'DIGITAL RESPONSES' sheet, they are not pulling through to the 'QUEUE' sheet and then they should be able to move and be removed from each page depending on progress box


Can anyone help please?

Cheers,

Ross

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.


--

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
--
Ross Whittome
Dad - Graphic Designer - Cub Scout Leader - DCC South London Scouts
Be Bold or italic, never regular
11 College gardens | Tooting | SW17 7UF | 07568383904









--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.


--
Trevor Scanlan
Technology Assistant
Shenandoah Community School District
304 West Nishna Road
Shenandoah, IA 51601

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1aa8f66e-d4cd-4b5d-b42a-2f55c2de8074%40googlegroups.com.

Arjun duraisamy

unread,
Nov 6, 2019, 1:31:44 PM11/6/19
to google-apps-sc...@googlegroups.com

Hi All,
I need to extract particular text from a cell value in a  column
Eg.TC-GLIB-12345-Simn_Run-001
     TC-GLIB-12356-Simn_Run1-002
Ans so on

 From abv example i need  only Simn_Run and Simn_Run1 and so on in all values in column    and paste in different sheet cell.

Please anyone help me.As i get stuck here for past one week.

Thanks !!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CABPHLXpKWxTxJHU9ksY%3D4mUmM4%2BtLcrkiPju6GVj%3Dba%2BzJSSYQ%40mail.gmail.com.

Clark Lind

unread,
Nov 7, 2019, 9:38:33 AM11/7/19
to Google Apps Script Community
Arjun, Please start a new thread/post for your question, otherwise no one will see it buried within this thread.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.


--

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.
--
Ross Whittome
Dad - Graphic Designer - Cub Scout Leader - DCC South London Scouts
Be Bold or italic, never regular
11 College gardens | Tooting | SW17 7UF | 07568383904









--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.


--
Trevor Scanlan
Technology Assistant
Shenandoah Community School District
304 West Nishna Road
Shenandoah, IA 51601

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.


--
Trevor Scanlan
Technology Assistant
Shenandoah Community School District
304 West Nishna Road
Shenandoah, IA 51601

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Arjun duraisamy

unread,
Nov 7, 2019, 9:46:50 AM11/7/19
to google-apps-sc...@googlegroups.com
Hi Clark,
Thanks for the info. I will do the same as you said hereafter.

But anyhow i got the solution by myself ☺️.

Thanks

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.


--

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
--
Ross Whittome
Dad - Graphic Designer - Cub Scout Leader - DCC South London Scouts
Be Bold or italic, never regular
11 College gardens | Tooting | SW17 7UF | 07568383904









--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.


--
Trevor Scanlan
Technology Assistant
Shenandoah Community School District
304 West Nishna Road
Shenandoah, IA 51601

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.


--
Trevor Scanlan
Technology Assistant
Shenandoah Community School District
304 West Nishna Road
Shenandoah, IA 51601

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1e66df2c-b3c5-4af2-85b6-934111da3c2f%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages