0

So I have the below function:

function filterSheet(sheetName, disputeStatus, disputeTypeArr, sourceSValues, removeColumnNumsArr){
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(sheetName);
  let values = sourceSValues.slice();


  if(removeColumnNumsArr){
    removeColumnNumsArr.sort((a, b) => b - a);
    for(let f = 0; f < values.length;  f++){
      for(let c = 0; c < removeColumnNumsArr.length ;c++){
        values[f].splice(removeColumnNumsArr[c]-1,1);
      }
    }  
  }

////////////////////////////////////////////////////////////////////////////////Delete prior data

  sheet.getRange(2,1,sheet.getLastRow(), sheet.getLastColumn()).clear({contentsOnly: true});

////////////////////////////////////////////////////////////////////////////////Paste updated data

  if(values.length !== 0){
    sheet.getRange(2, 1, values.length, values[1].length).setValues(values)
  }
};

This function is called multiple times like this:

function splitDataToTabs(){

// Source data variables
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sourceSheet = ss.getSheetByName("BQ Data");
const sourceSheetValues = sourceSheet.getRange(2, 1, sourceSheet.getLastRow()-1, sourceSheet.getLastColumn()).getValues();

Logger.log(sourceSheetValues[1].length)

//Filter and paste to 'New' sheet
filterSheet("New", "New Query",["CC FIRST TIME FIX" , "Payment Charge Referral" , "DD Charge Addition" , "No response to letter raise admin fee" , "Customer querying admin fee"], sourceSheetValues, [9,10,11,12]);

Logger.log(sourceSheetValues[1].length)

//Filter and paste to 'Res' sheet
filterSheet("Res", null ,["Awaiting Legal Auth" , "DEBT RECOVERY - Branch COVID Deal" , "DEBT RECOVERY - Termination Request" , "Payment Charge Referral" , "DD Charge Addition" , "No response to letter raise admin fee" , "Customer querying admin fee"],sourceSheetValues, [9,10,11,12] );

Logger.log(sourceSheetValues[1].length)

The problem im having is that the variable 'sourceSheetValues' is getting changed after each call. So when 'sourceSheetValues' is passed into filterSheet for the second time, it already has columns 9,10,11,12 removed.

Logger.log(sourceSheetValues[1].length) is logging 15.0, 11.0, 8.0 when function splitDataToTabs is run.

This feels like im missing something obvious so apologies in advance if thats the case but I cant figure it out.

Connor Stokes
  • 13
  • 5
  • 1
    What is your goal? – Cooper Oct 20 '22 at 13:29
  • @Cooper So im trying to filter data from a source sheet and paste it into multiple other sheets, but each sheet has its own filter criteria. The filter sheet function does this, but unexpectedly its changing sourceSheetValues every time it is called which is causing issues. Might look like there are some arguments passed which do nothing but thats just because ive commented out the code that I know works fine for readability. – Connor Stokes Oct 20 '22 at 13:32
  • Okay then I have to ask what is the goal of each filtration configuration and how do they correlate? – Cooper Oct 20 '22 at 13:33
  • @Cooper So I want the code to take the initial array that is passed into it (sourceSValues), modify the array by removing all columns in the removeColumnNumsArr array, and then paste those array into the passed in sheet. What currently happening though is that the array modifcation is somehow being applied to the variable sourceSheetValues outside of function as far as i can tell so when this variable is passed in a second time it is alreadt 4 columns shorter. – Connor Stokes Oct 20 '22 at 13:40
  • Does each sheet get a different stream of columns? – Cooper Oct 20 '22 at 13:45
  • @cooper Yes, it needs to be called 3 more times, with different columns needing removal. – Connor Stokes Oct 20 '22 at 13:48
  • Is the original source data always the same? – Cooper Oct 20 '22 at 13:49
  • @cooper So the source data sheet does change daily once, and its outside of this section of code in a completely seperate function. Within this section of code, no the source sheet shouldnt be changing. – Connor Stokes Oct 20 '22 at 13:53
  • Well the map array method was originally created for this sort of conversion and I would think that you will need some sort of configuration information to control which subset of columns is provided. Personally I don't work well in this sort of ambiguous environment. So unless you have specific requirements in mind I probably will have to let someone else assist you. – Cooper Oct 20 '22 at 14:01
  • You need to deep copy `sourceSheetValues`. Note that it's a 2D array. So, techniques like `slice()` or `...` etc needs to be looped into. Probably the easiest(without looping) is `JSON.parse(JSON.stringify(sourceSheetValues))`. Send the copy to another function instead of original. – TheMaster Oct 20 '22 at 14:18
  • @theMaster thank you will try this now. First im learning about deep copys. – Connor Stokes Oct 20 '22 at 14:25

0 Answers0