CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Chris Wheatland
Chris Wheatland

Reputation: 3

Pull specific data from many google sheets to a master

I'm trying to create an equipment booking system using Google Sheets and script. I'm reasonably new to this so I'm looking for guidance rather than the solution laid out, I like to learn!

I have 8 science teachers who each need to order apparatus for their lessons. I've created a spreadsheet each for them which contains their timetable, the idea being that they type their equipment request into the relevant box on their timetable.

What I now want to do is to send any completed box as a set of data (including things like the date required, teacher's name, and of course the equipment required) to a master sheet seen by the technicians. The format will be totally different, I do not want to just import the range from one sheet to another (that'd be easy!), just the data it contains.

I want the master sheet to behave like a google form-linked spreadsheet ideally, but to take data from the 8 input spreadsheets as its input data rather than from a form (which I decided was a too cumbersome method of input).

I've searched for a long time and come up with nothing, importing whole sheets, or complete ranges would be easy but I want the master sheet to be able to format and reorder the data itself.

I wondered whether I could put the data I need into an array, pass that array to the new sheet and have the new sheet forget where it came from.

I'm looking for advice on whether this is possible and if so a direction to go in in order to figure it out rather than a complete solution to this problem. Thank you!

Upvotes: 0

Views: 163

Answers (1)

Cameron Roberts
Cameron Roberts

Reputation: 7367

The approach I suggest is to create a script within your Master sheet. Open the external spreadsheets using SpreadsheetApp.openById() or SpreadsheetApp.openByUrl().

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#methods

Once you have opened the spreadsheet, retrieve the data from the relevant sheet(s) by opening the sheet, retrieving the relevant range(s), and pulling the data into arrays using getValues()

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetbynamename

https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow-column-numrows-numcolumns

https://developers.google.com/apps-script/reference/spreadsheet/range#getvalues

You can then process the data in the array as you mention in your post, build an array for outputting, and write it to your master sheet with Range.setValues()

Upvotes: 1

Related Questions