Google spreadsheet ImportRange - need to improve my workaround
I am pretty new to this forum and also to the google scripts. I have never
actually learned JavaScript or any other programming language, however I
was forced to start using then since the time I chose the google apps as
my main platform for my small business.
My problem is in google ImportRange function limitation to 50 on every
spreadsheet. I created a model, where every customer has his own
spreadsheet with his personal data, deadlines, etc. located in his own
folder in google drive.
I also created a spreadsheet called "Organizer", Organizer has two
functions -
1) create automatic correspondention using autoCrat script, 2) show
deadlines for every costumer and sort them by priority.
So i need to be able to share / import / copy data from all customer
spreadsheets to the "Organizer". Because there are 50+ costumer
spreadsheets, I had to give up on ImportRange function and instead of that
I am using a simple script to copy files from every spreadsheet directly:
function ImportDataRange() {
// rown number.1
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Databaze");
var range = sheet.getRange(2, 1)
var id = range.getValue()
var ssraw = SpreadsheetApp.openById(id);
var sheetraw = ssraw.getSheetByName("Raw");
var range = sheetraw.getRange("A2:AB2");
var data = range.getValues();
sheet.getRange("B2:AC2").setValues(data)
// row number.2
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Databaze");
var range = sheet.getRange(3, 1)
var id = range.getValue()
var ssraw = SpreadsheetApp.openById(id);
var sheetraw = ssraw.getSheetByName("Raw");
var range = sheetraw.getRange("A2:AB2");
var data = range.getValues();
sheet.getRange("B3:AC3").setValues(data)
}
This script actually works well, but problem is, when I want to add new
costumer spreadsheet to "Organizer" using this method, I have to manually
add new copy of whole code for every new row and also change the output
range of imported data and location of source file ID in "Organizer".
Does anybody know some kind of workaroud, which will help me to add new
rows / costumer data easier / automatically ?
Thank you for your help!
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHello +hiren Jani,
ReplyDeleteI am also learning by doing and with no previous knowledge neither in computer sciences nor in programming languages. So probably I won't help much.
It seems for me that one way around would be using DocsLists to find new documents, then do a loop to each document directly with: a trigger to populate your main organizer sheet onOpen; or pushing a menu option.
Another less automated/complex way would be to turn your script into a "custom function" type, as for example:
function ImportDataRange(customerfilename) {
...
var ssraw = SpreadsheetApp.openByName(customerfilename);
....
and "return range" instead of the latter part of the code including "setValues".
then you could invoke the custom function directly on the spreadsheet, as if it were another function. You would need to adapt your code a little bit more than what I exposed, I think; and you would trim all the repetition for rows.
I am not sure though that your script would be allowed to turn into custom function; doesn't seem to hard to give it a try.
Good luck!