Google Sheets copy & paste special values script

Question

I use Google Sheets as our main tool to dispatch construction trucks and communicate with our scale clerks the "who and what they will be picking up". I have researched for days on numerous sites about copying & pasting but my problem is the FROM and TO have the same data range (B8:B38) BUT...... the sheets names are rarely the same OR it will be the same sheet name but from a different file (files saved by calendar date).

Ideally a script that would blindly copy selected information from one active sheet [data range B8:B38 if required to make script work] (assume different file name) to another active sheet data range [B8:B38] (assume different file name).

Being realistic two individual scripts that I could assign "buttons" to mimic the keyboard shortcuts CNTRL + C & CNTRL + SHIFT + V are my best option. My clerks are all older and technology isn't their strong suit, but if they could click a button to copy and jump to a different sheet and click a button to paste they could easily handle the task.

---

I have tried macros too, but no luck working around the sheet names and/or filenames for my scripting ability.

Answer 1

You might use

• SpreadsheetApp.getActiveSheet() to get the active sheet
• SpreadsheetApp.getSheetByName(sheetName) to get a sheet by name
• SpreadsheetApp.getUi().prompt(...) to ask the user the FROM / TO as well custom dialogs and sidebars.

---

You might have to start by reading https://developers.google.com/apps-script/guides/sheets and https://developers.google.com/apps-script/guides/sheets/macros. Briefly,

• Google Apps Script use JavaScript as programming language.
• The only way to assign a script to a keyboard shortcut is by using the Google Sheets Macros feature but they are complex (Ctrl+Alt+Shift+Number).

Please be aware that:

• Using images over the grid as buttons might not be very reliable as they could be easily deleted or moved from their position.
• Scripts created by the Google Sheets macro recorder are based on "active" sheets and ranges which makes the code hard to adapt and perform very poorly (they're very slow and require that the user keeps their "hands" away from the spreadsheet while the macro is running.
• Macros can be executed from the Macros menu
• Scripts could be imported as macros. This might be handy to avoid having to create a custom menu and having to use the Macro recorder.

Considering the above,

1. it might be better to use menu options than keyboard shortcuts and buttons
2. if you are able to create a Macro close to what you need you might find worthy to improve your JavaScript knowledge just enough to be able to improve and adapt the recorded macro to fit your needs (or ask more specific questions about using scripts in Google Sheets.)