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!
No comments:
Post a Comment