0

I have a spreadsheet a list of 15 spreadsheet that I have to update weekly with fresh data.

At present I do this manually however having a script will make the task so much easier.

What I have is a spreadsheet that contains the master data, with a separate tab that contains a list of the spreadsheet ID's in Column A and a list of the filter condition that corresponds to that ID in column B.

What I want to do is have a script that first clears the sheets I am copying to, then filters my master sheet using the condition in Column B on sheet2 then pastes that information into sheet1 of the spreadsheet ID located in Column A of sheet2.

I have pasted a link to some test sheets that will hopefully give a better idea of what I am trying to achieve.

Data Sauce https://docs.google.com/spreadsheets/d/14jgvclYI389opkPGmNYstaTJ2DojIyf6Nl2jXbQH5_s/edit?usp=sharing

Result Data 1 https://docs.google.com/spreadsheets/d/1tY6CZFQh68afX_O4E1csmGk40Fiyuy_WOJbzGW0cjJE/edit?usp=sharing

Result Data 2 https://docs.google.com/spreadsheets/d/1UzwVsnntUNShBkjnII2Tt34Datl3UUy8x9RoSE5AGdw/edit?usp=sharing

Code V2,

So I think I am nearly there with this code, the Filter does not apply and its not picking up the id from the cell, but I can feel I am close.

Just wish I could find that final bit to get it right.

function get_data(){
  var s1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("MasterData");
  var s3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("URL's")
    var range = s1.getRange(2, 1, s1.getLastRow(), s1.getLastColumn())
  var filter = range.getFilter() || range.createFilter();
  var foo_index = 1; // column A
  

  var data = []
  for (var j = 0;j<s3.getLastRow()+1; j++){
   var filterName = s3.getRange(j+1, 2).getValue()
   var filterValue = SpreadsheetApp.newFilterCriteria().whenTextEqualTo(filterName).build()
   var id = s3.getRange(j+1,1).getValue()
   for (i = 1; i < s1.getLastRow(); i++){
    if(!s1.isRowHiddenByFilter(i)) {
      var row_data = s1.getRange(i+1, 1, 1, s1.getLastColumn()).getValues()
      data.push(row_data[0])
      var s2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(id)
      var clear = s2.getRange(2,1,s2.getLastRow(),s2.getLastColumn()).clear({contentsOnly: true})
      var paste = s2.getRange(s2.getLastRow()+1,1,data.length,4).setValues(data)
    }
    }
  }
}
6
  • Welcome. Please read how to and share a test sheet so as you can be easier helped. – marikamitsos Feb 13 at 20:36
  • @marikamitsos I will update my question with a test sheet. – Daniel Lawrie Feb 13 at 21:55
  • 1
    Thank you for the sheet. Still. Please show/write what the expected results be. Also. Please do not have 40000 rows. Try 100 or even less that will represent your situation. Otherwise it is difficult to test it. Then. let us know through a comment. – marikamitsos Feb 13 at 22:15
  • 1
    Also show what you tried and add a brief description of your search efforts as is suggested in How to Ask. – Rubén Feb 13 at 22:29
  • I have updated my question again with two test sheets containing result data, unfortunately I can not tell you what I have tried already as I do not know where to start with this script, as its not a simple copy and paste script – Daniel Lawrie Feb 14 at 8:01

Your Answer

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

Browse other questions tagged or ask your own question.