0

I am trying to gather data from multiple tabs of different sheets, but a few of my files have the columns in different orders so some of the data is showing up in the "wrong" column (Column C on my master file is Date, but Column C is revenue on one of the files I am grabbing data from). The names of the columns in all files are consistent, just not the order.

This is the code I have so far, but I am not sure how to accommodate the change in column order since for my purposes I can't open every file and rearrange the order manually.

function getDataFromSpreadsheet(ssID){

  var ss = SpreadsheetApp.openById(ssID)
  var allsheets = ss.getSheets();

  var data = []

  for(var s in allsheets){
    var sheet = allsheets[s];
    if(
       (sheet.getName() == "Summary") || 
       (sheet.getName() == "Data") 
    )continue;
    {
      var values = sheet.getRange("A2:G").getValues();
      data = data.concat(values)      
    }
  }
  return data;
}

I also need it to account for the fact that I don't want the headers copied every time, just the data under them (the A2:G range). How can I grab multiple columns by name and assign to an array in the order that I want while still maintaining my for loop?

4
  • The names in the script are skipping the sheets that I do not want data pulled from, just an FYI
    – arigreat
    Jul 9 at 20:09
  • “ a few of my files have the columns in different orders”. How many files is a “few”? and are these sheets, and their column order, predictable?
    – Tedinoz
    Jul 10 at 8:04
  • @Tedinoz 2-3 of my 30 files have the same columns as the other 28, but in a different order. This in itself makes the column order unpredictable as all the files contain the same type of content, and the only thing different is the data values themselves and the order of the columns. I can't change the order of the columns within the original file, either.
    – arigreat
    Jul 12 at 12:07
0

This may not be the most effective way of accomplishing my goal, but I ended up making individual functions (essentially multiples of the above function) for each column of data I'm drawing from. I then have to assign each individually to the master sheet.

It's a lengthy process and takes a while to run, so I am still looking for a faster way of accomplishing this. I'm not sure how to assign arrays as columns within another array without assigning them to a sheet first.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.