0

I have data in Google Sheets that I need to clean. One column in particular is may contain too many characters and I'd like to take the first 200 characters only if its 201+ long.

I found a way to do it, but it's so nasty that I can see the cells being corrected 1 by 1... visually. I have 100k+ lines to process so my solution is not really an option.

here's the code for now, not working. I'm working on the range.Value = LEFT(range.Value,200); line

function myFunction() {
  var maxRow = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getLastRow();  
  removeOverLenght(4, maxRow);  //COLUMN D index = 4  
}



function removeOverLenght(column, maxRow){  

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  var rangeArray = [];

  rangeArray = activeSheet.getRange(1,column,maxRow,1).getValues();

  for(var i=0;i<rangeArray.length;i++){

    rangeArray[i] = rangeArray[i].toString().substr(0, 200);

  }

  activeSheet.getRange(1,column,maxRow,1).setValues([rangeArray]);     // I get an error saying that the length is 1 but should be 74 (maxRow value)
  //activeSheet.getRange(1,column,1,maxRow).setValues([rangeArray]);   //This code runs without error, but the values are pasted on the same row, on 74 different columns, flipping from vertical to horizontal which is nonsense
}

I have searched and read tons of articles, but couldn't get the sub-string functions to work either, for some reason I can't even access them. Also, if there's a way to do all the cells at once without doing a for() function any leads are greatly welcome.

EDIT: I added the solution proposed and it works for the substring. However while trying to do it as an array, the result is that all cells get the same value (coming from D1), even though the getValue is part of the for(). Any idea what I might be doing wrong? I tried to base my code on the best practices, but I really don't know what they are doing differently

EDIT2: i could get my code further by using getValues() outside of the for() loop, treating the values in the loop and using setValues([Array]). The only problem is that it doesn't let me set the values in the same range, I need to switch getRange(1,column,maxRow,1) to getRange(1,column,1,maxRow) which is nonsense because it flips the column to a row. Any idea? I've been reading around 4 hours on Arrays and documentation available is horrible

Mathieu J.
  • 11
  • 1
  • 1
    Why don't use the Google Sheets built-in [LEFT function](https://support.google.com/docs/answer/3094079?hl=en)? Also try [ARRAYFORMULA](https://support.google.com/docs/answer/3093275?hl=en). – Rubén Oct 15 '18 at 22:18
  • Search "Best Practices" in the documentation – TheMaster Oct 15 '18 at 22:29
  • Ruben, I'm trying to create a script. the Left function is available in googlesheets, not in googleapps script – Mathieu J. Oct 16 '18 at 00:08
  • `setValue` and `setValues` are quite different. Same for `getValue` and `getValues`. – tehhowch Oct 16 '18 at 02:13
  • https://www.w3schools.com/js/js_arrays.asp – TheMaster Oct 16 '18 at 12:20
  • if I try the getValues instead of getValue I get this error: TypeError: Cannot find function substr in object fewfwfwef (first cell in the range). The setValues brings this error: Cannot convert Array to Object[][] – Mathieu J. Oct 16 '18 at 12:27
  • [Edit] to show modified code or Ask a new question with relevant code and extensive details. – TheMaster Oct 16 '18 at 13:15
  • Consider a 2 dimensional array. Your `rangeArray` will look like this [[A1],['A2'],['A3']...] https://stackoverflow.com/a/51515924 You're looping over rows, but not columns. – TheMaster Oct 16 '18 at 14:04
  • Consider what would be a "row" 2D array and what would be a "column" 2D array. You can test with code, too - `console.log` paired with `sheet.getRange("A1:A5").getValues();` and `sheet.getRange("A1:E1").getValues();` – tehhowch Oct 16 '18 at 15:48

1 Answers1

0

I'm not sure why the substring functions aren't working for you. I'm thinking it might be because the values you're reading aren't being interpreted as strings.

Try this:

function removeOverLenght(column, maxRow){  
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  
  for(var i=1;i<maxRow+1;i++){
    var range = activeSheet.getRange(i,column),
        // ensure you're working with a string
        value = "" + range.getValue();
    range.setValue(value.substr(0, 200));
  }  
}
rmccreary
  • 76
  • 6
  • thanks it's working. I think if I try to combine with an array function as proposed by Ruben it might work great – Mathieu J. Oct 16 '18 at 00:11