I have a workbook with roughly 15 different tabs on it, however, data input takes place on just two sheets, let's call them SheetA & Sheet B for clarity.
The data on Sheet A is entered in a handful of non-sequential columns. The data on Sheet B is entered in a variety of places, but only in single cells and each of those input cells is also a named range.
I'm trying to find an effective way for people to move data from one workbook to another workbook in this context.
I have a versioning tool built and notifications and such so that people will see when a new version is available.
My plan of attack is this:
On the new workbook - create ImportSheetA & ImportSheetB. Allow the person to enter the url of their current sheet, use import range to grab the correct data.
SheetA will use ImportRange to grab what ends up in ImportSheetA. since all of the arrays and such are outside the data range (aka they are all in header rows), I'll just instruct the user to copy/paste the data in the live SheetA so that the values become hard coded. <might also be able to dispense w/ ImportSheetA and go directly into sheet A.
SheetB will do something very similar but there are really only a handful of variables (maybe 10) that are set in that sheet. As stuff will move around, I'll create NamedRanges on ImportSheetB --- use a handful of import functions on the live Sheet B and color code them so that people know to copy paste.
That's the only solution I am both currently aware of AND technically capable of deploying at this time but I have to assume there's something better (and there might even be a better solution that I am capable of deploying that I don't know of).
A couple of pieces of information:
- On ImportSheetA, would i be able to write a query that pulls in the correct cols from the original Sheet A or would i need to write a series of import functions - one for each range - b/c they have to be put back in the same non-sequential order they came in as some cols are hidden and not useful for copying purposes.
- I've looked for Add-ons - didn't see anything to do this.
- The workbook already contains a ton of import functions - mostly from random websites and it is ultimately very dirty. I don't think this is a big deal b/c this import only takes place once and after it's done the ImportSheets can be trashed.
- I'm good enough to be able to install ImportJSON - so i have that working - i would be open to using something like that but my question would be do i need to append any additional scripts to the same ImportJSON or would I need to create another? Would love a tutorial or online resource I could go learn how to do that with.
I do have a demo version available i can link people to if they'd like to see it.