stop numbers in column to changing to scientific notation when moving in in a script

Question

I have a Google sheets script which auto formats a spreadsheet. The issue I am having is when I move a column, the numbers in that column change to scientific notation. How can I keep that from happening. I tried setting the column data type as a string and as a number but neither one works. After the column moves, the values are returned in scientific notation.

Here is my code below:

//Set CC number as string data type
  var ccType = sheet.getRange("K2:K");
  ccType.setNumberFormat("@");

//Move the credit card and last 4 of CC columns next to the name column
  var columnsCC = sheet.getRange("K1:L1");
  sheet.moveColumns(columnsCC, 6);

Have you checked [Number format patterns](https://developers.google.com/sheets/api/guides/formats#number_format_patterns) in GSheets documentation? — MαπμQμαπkγVπ.0, Mar 07 '19 at 08:52
`ccType.setNumberFormat("@");` this formats the column as text. have you tried `ccType.setNumberFormat('0.###############');`? You don't say how many digits in the number, but refer BenFletcher's own answer at [Applying “Automatic” number formatting](https://stackoverflow.com/a/38067113/1330560). BTW, I didn't have your experience of numbers reverting to scientific notation. (That's a specific number format, you know). — Tedinoz, Mar 10 '19 at 03:05
I ran the script with just formatting the column without moving it. I checked the format and it is plain text. But when I add the code to move the column, same thing happens. The numbers turn to scientific notation. So weird and frustrating — Tarik Hodzic, Mar 14 '19 at 16:07

score 0 · Answer 1 · answered Mar 15 '19 at 18:14

Generate 20 - 16 digit numbers. Format them. Then Move Column

I've never used the sheet.moveColumn() command before. I found it rather interesting. Anyway this function generates total of 20 16 digit numbers in A1:A20 and moves them to column F. Interestingly enough they end up in column E since it also deletes columnA. I used a generateString() function that I wrote previously to generate the sixteen digit numbers.

function formatThenMove() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getRange(1,1,20,1);//A1:A20
  var vA=rg.getValues();
  var fA=rg.getNumberFormats();
  for(var i=0;i<vA.length;i++) {
    vA[i][0]=generateString();//setup for 16 digits
    fA[i][0]="@";//here's the format for raw text
  }
  rg.setValues(vA);//set values
  rg.setNumberFormats(fA);//set formats
  sh.moveColumns(rg, 6);//move to column F
}

function generateString(sObj){
  var sObj=sObj || {uppercase:false,lowercase:false,numbers:true,allothers:false,custom:false,custxt:'',slctlen:16};//defaults to 16 digit number
  var upperCase='ABCDEFGHIJKLMNOPQRSTUVWXYZ';//26
  var lowerCase='abcdefghijklmnopqrstuvwxyz';//26
  var numBers='0123456789';//10
  var allOthers='~@#$%^&*()_-+={[}]|\:;"<,>.?/';//29
  var s='';
  var o='';
  s+=(sObj.uppercase)?upperCase:'';
  s+=(sObj.lowercase)?lowerCase:'';
  s+=(sObj.numbers)?numBers:'';
  s+=(sObj.allothers)?allOthers:'';
  s+=(sObj.custom)?sObj.custxt:'';
  for(var i=0;i<sObj.slctlen;i++){
    o+=s.charAt(Math.floor(Math.random()*s.length));
  }
  Logger.log(o);
  return o;
}

I don't see the behavior you mention about the format changing to scientific notation. So you didn't achieve a minimal complete verifiable example.

Formatting Reference

stop numbers in column to changing to scientific notation when moving in in a script

1 Answers1

Generate 20 - 16 digit numbers. Format them. Then Move Column