getValues() and setValues() - getting unformat data

Question

I want to substitute the ImportRange function that I have on a gsheet with apps script. Therefore, I am using methods such as getValues() and setValues(). However when I run the script there are rows of the first column of the range that get unformatted. The original data format is string but in some rows I get kind of dates format (see the link with a screenshot). For example, in row 67 I should get there a link like http://admin.(.....)/73100.

Screenshot of the data returned by the script

function paste_main_query() {
  var ss=SpreadsheetApp.openById("(ID)");
  var destination = ss.getSheetByName("STM_query");


  var import_range_aux= destination.getRange('A2:A').getValues();

  var numRows = import_range_aux.filter(String).length;
    
  var import_range = destination.getRange('A2:G'+numRows).getDisplayValues()
  var sorted_1 = ArrayLib.sort(import_range,6,false) // ascending column 4
  var sorted_2 = ArrayLib.sort(sorted_1,3,true)      // ascending column 4
  var sorted_3 = ArrayLib.sort(sorted_2,0,false)     // ascending column 4
  var destination = ss.getSheetByName("import_range_aux");
  destination.getRange('A2:G').clearContent();
  destination.getRange(2,1,sorted_3.length, sorted_3[0].length).setValues(sorted_3);
}

Notes:

I have already tried both methods: getValues() and getDisplayValues()
I have already used the importrange variable on the last row of the code and I got the same errors, so the problem is not from sorting the data with the ArrayLib library

Do you have any idea of what I can be doing wrong here? Any tip is more than welcome.

Have you tried changing the format of the cells? Format > Number > Select your formatting — I hope this is helpful to you
Consider sharing a publicly editable sample spreadsheet with realistic-looking source data, and showing your hand-entered expected results in another tab in the same spreadsheet. — doubleunary

doubleunary doubleunary · Accepted Answer · 2021-05-10T09:22:27

The screenshot suggests that some of the data are text strings that get interpreted as dates by ArrayLib or by Google Sheets. This can happen with data like 1 7000 that gets interpreted as 1 January 7000.

My recollection is that Google Sheets will not autoconvert text strings that you read with getDisplayValues() and write with setValues(), so I would recommend that you try your script once more without ArrayLib to verify that you can read and write the data correctly in the first place.

You may want to take a look at the the importDataFromManySpreadsheets script for an example of how to do this.

I think it is also worthwhile to mention that the whole thing can probably be done with the built-in importrange() spreadsheet function, wrapped in a query() to sort the data. It would be something like this:

=query( importrange("...", "STM_query!A2:G), "order by Col1 desc, Col5 asc, Col7 desc", 0 )

getValues() and setValues() - getting unformat data

1 Answers