Google Sheets Macros and GAS

[Marty Mackay]

Hey all!

I am working on automating a google sheet that shows collections, and runs a weekly macro to generate forecasts. I have this macro working fairly well, but I need a way to move all of the rows matching multiple conditions from one sheet to another, (another tab on the same sheet, not a different file) the common response is the following- https://support.google.com/docs/thread/39992635?msgid=40432488

Which I'm sure is great but triggers based off of an edit rather than a certain time, and also only filters based off of a single value per destination sheet, rather than the several that I need.

The use case is copying the forecast/actuals table for collections and moving it down for historical data, then recalculating the forecasts. In order to calculate these, I need to move completed jobs off of the table, based off of a couple of columns. 

Is there a way to easily do this with macros? Or is this more viable with scripting?

Thanks in advance!

[Keith Andersen]

Yes it is easily possible to write a script that pulls the data table into an array and then via a for loop, loops through the data array and saves to a new array the data which you are filtering. Then write that array back to a different sheet or to the very same sheet. 

Would you be able to share your sheet so as to write the code accurately and without guessing?

Keith

--
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/ab59e19c-869b-4860-b3c4-10efdc20818en%40googlegroups.com.

[Marty Mackay]

Just to clarify- are you suggesting that I write the table into an array, then filter the array once to select only what I want to move, putting it in the destination table, and filtering again on the original array selecting everything BUT what I moved, so I can overwrite the original sheets contents, removing the items that were moved? I can't share the sheet unfortunately, as it deals with collections, but I really appreciate the help!

Thanks,

Marty

[Keith Andersen]

I will work on a mock table and script for you complete with detailed notes. I will post it later today / tonight. 

Hopefully that will help.

Cheers

Keith

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/37a9aa5e-b8a8-4196-b577-6e12048002b5n%40googlegroups.com.

[Marty Mackay]

Thank you Keith! No need to go to all that trouble unless you find it fun.

Thank you for all your help!

[Keith Andersen]

Yes it is fun. 👍

But to answer your question, yes - once you've loaded the data into an array, you can do a for loop and filter the data any way you please. You would create a temporary empty array and then using the push method, push to the temporary array that data you need separated. You would then take that temporary array and write it back to the original sheet if you so desired or to an entirely different sheet (destination).

You could even create two or three temporary arrays and through the for loop simply do testing upon each array element and place it in the array you want to and then write all three to whatever destinations you want it.

The key to make this fast and effective is gathering the original data into a an array. Then working on that array in the script through a for loop.

I hope that helps you get started and again I will work on that example.

Cheers again.

Keith

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8415cb7f-c52a-4fd5-b781-1838c356b090n%40googlegroups.com.

[Keith Andersen]

Marty,

https://docs.google.com/spreadsheets/d/1f9rgIyvHmHi5D4MkMR1NinelNxBKQvWgUlw4v2vNDRI/edit?usp=sharing

The sheet is view only. Make a copy of it and it will become yours with full editor privileges. You will need to initially run the spc_menu function to place the Special Menu in the upper tool bar. You wll need to give script permissions.

Here is a video I made explaining the sheet and the script.  https://youtu.be/d2YMgAroHGo

Hope it helps.

Cheers 

Keith.

[Shrivastava, Poonam]

Hi Keith,

Can you please share the file witn me too. I am unable to access.

Thanks & Regards,

Poonam Shrivastava

VBG Vendor Engagements

Global Technology Solutions

Verizon Business Group

M: +91 703-291-6454

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