CODEWITHSUNDEEP
loopsgoogle-apps-scriptgoogle-sheets
Mattis.Engstedt
Mattis.Engstedt

Reputation: 11

Get data from a lot of different sheets with a script in Google Spreadsheet

I have a spreadsheet containing 50 sheets, named "1", "2", "3". The very first sheet is a summary, gathering specific information from the different sheets. The simple way to fill it in is by using ='1'!B5 and so on. What I want to do is enter that formula into a script and have it pull the specified data from all the different sheets on new rows. So that info from sheet 1 is at row 1, info from sheet 2 is on row 2.

The reason I don't want to do it manually is that I have 14 cells that I need to pull and insert into 4 different overviews from 50 sheets and 2 spreadsheets where I need to do this. Adding up to 5600 functions that I would need to change manually.

The reason I named the sheets in this simple manner is that I know it enables me to write this kind of function. A variable that increases its value by 1 for every loop and outputs the function on a new row with every loop, for instance

var sheetName = 0; output.setValue('='' + sheetName + ''!B5');

I know I should have a variable for the specified output cell that also increase its value by 1 for every loop. But I have no idea how to get this sorted out, my scripting skills only allow me to modify others script to make them suit my own purposes but not write anything by myself and I have been unable to figure this one out more then the theory behind it. I know this isn't the place to ask for ready made code so I don't demand or even expect a solution, I just felt I had to try and ask for aid.

Here is a dummy which shows what I'm trying to do. https://docs.google.com/spreadsheet/ccc?key=0ArjDeqGD779cdEh2Ynh5ODBFQVFkVE9lX0p4aWpXelE&usp=sharing#gid=0

Upvotes: 0

Views: 11482

Answers (1)

Sam Scholefield
Sam Scholefield

Reputation: 1250

You are a lucky man Mattis! I was doing this thing myself earlier this week so I have added a full working script to your dummy sheet. I'll add a copy here for completeness. Any questions please let me know :)

function populateOverview() {  

  //set basics
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var viewSheet = ss.getSheetByName('Overview');
  var dataLength = 4; //this is how many cells from each sheet

  //create array of sheet names and remove 'overview' 
  var sheetNames = getSheetNames();
  var overviewIndex = sheetNames.indexOf('Overview');
    sheetNames.splice(overviewIndex, 1);

  //set initial data and target columns  
  var dataColumn = 2;
  var dataRow = 2;
  var targetColumn = 3;

  var j = 0;

  //outer loop iterates through sheets
  for (var i = 0; i < sheetNames.length; i++){

  //inner loop works across the columns in each sheet, and across the column on the overview
    for(var k = 0; k < dataLength; k++){    
  viewSheet.getRange(j+3, targetColumn+k).setValue(ss.getSheetByName(sheetNames[i]).getRange(dataRow, dataColumn).getValue());
      dataColumn = dataColumn + 2;

    }
    //increments row counter for next sheet input
    j = j+1;
    //resets for next sheet
    dataColumn = 2;  
    targetColumn = 3;  
  }

}

/*return sheetnames as array*/
function getSheetNames(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetArray = ss.getSheets();
  var sheetNameArray = [];

  for(var i = 0; i<sheetArray.length; i++){
    sheetNameArray.push(sheetArray[i].getSheetName());
  }

  return sheetNameArray;
}

Upvotes: 1

Related Questions