Function that shifts columns written in JavaScript not working in Google sheets

Question

I have a function in javascript gotten from here

I tried to put it into the proper format for Google sheets with the sparse knowledge I have but have not gotten it to work

The function shifts columns in an array from indices in a list I am using chrome V8

The function gives the error Cannot read property length of undefined for sht2.getRange(1,1, result.length, result [0].length).setValues(result )

Thanks

This is what id does

Bob|Carol|Ted|Alice
a  |b    |c  |d
1  |2    |3  |4
A  |B    |C  |D

If the list of indices is list=[3] so I get

Alice|Bob|Carol|Ted
d    |a  |b    |c
4    |1  |2    |3
D    |A  |B    |C

list = [2,3]

Ted|Alice|Bob|Carol
c  |d    |a  |b
3  |4    |1  |2
C  |D    |A  |B

function test() {
  var ss   = SpreadsheetApp.getActiveSpreadsheet();  
  var sht  = ss.getSheetByName('users');
  var rng  = sht.getDataRange();
  var data = rng.getValues();
  
  var tofront = [2,0,28,4]
  var result = rearrange(data, [2,0,28,4])

  var sht2 = ss.getSheetByName('AAA');
  sht2.getRange(1,1, result.length, result [0].length).setValues(result );
}


function rearrange(rows, tofront) {
 var rearrange = (rows=[], toFront=[]) => {

  var originalHeaders = [...rows[0]]

  var remainingHeaders = [...rows[0]]

  var frontHeaders = toFront.map(index => {
    // set this one to null, to be filtered out later
    remainingHeaders[index] = null
    // grab the original header value
    return originalHeaders[index]
  })
  // you don't want to modify the original headers directly,
  // seeing as you might not end up with the desired result
  // if you pass a `toFront` value like [1,2] or where
  // any of the values come after another
  
  var newHeaders = [
    ...frontHeaders,
    ...remainingHeaders.filter(v => v !== null)
  ]
  
  return data.map(r => {
    let row = {}
    r.forEach((value, i) => {
      row[originalHeaders[i]] = value
    })
    
    return newHeaders.map(h => row[h])
  })
 }
}

You're not writing back to the spreadsheet – Diego Nov 20 '20 at 14:57 — Diego, Nov 20 '20 at 14:57
sorry, just updated the function – Einarr Nov 20 '20 at 14:57 — Einarr, Nov 20 '20 at 14:57
`rearrange()` doesn't return anything – Diego Nov 20 '20 at 15:43 — Diego, Nov 20 '20 at 15:43

Diego · Accepted Answer · 2020-11-20T16:02:22.220

The main problem is that you're not returning anything from rearrange(). The other issue is that you're not actually executing the rearranging logic but actually just saving that function into the variable rearrange.

function rearrange(rows, tofront) {
 var rearrange = (rows=[], toFront=[]) => { /* rearranging logic */ }
 
 // Actually execute the logic and return its output
 return rearrange(rows, tofront);
}

There's no need to save the logic in the rearrange variable inside of the rearrange() function. You can just take that part out.

function test() {
  var ss   = SpreadsheetApp.getActiveSpreadsheet();  
  var sht  = ss.getSheetByName('users');
  var rng  = sht.getDataRange();
  var data = rng.getValues();
  
  var tofront = [2,3]
  var result = rearrange(data, tofront)

  var sht2 = ss.getSheetByName('AAA');
  sht2.getRange(1,1, result.length, result [0].length).setValues(result );
}


function rearrange(rows = [], toFront = []) {
  var originalHeaders = [...rows[0]]
  var remainingHeaders = [...rows[0]]

  var frontHeaders = toFront.map(index => {
    remainingHeaders[index] = null
    return originalHeaders[index]
  })
  
  var newHeaders = [
    ...frontHeaders,
    ...remainingHeaders.filter(v => v !== null)
  ]
  
  return rows.map(r => {
    let row = {}
    r.forEach((value, i) => {
      row[originalHeaders[i]] = value
    })
    
    return newHeaders.map(h => row[h])
  });
}

* You were also referencing data in your penultimate map(), but that should have been rows as indicated above.

This is sweat!! Thank you. Diego, why use `forEach` and not `map` Thank you again — Einarr, Nov 20 '20 at 18:28
@Einarr Glad it works. I recommend that you ask the original author as I didn't inspect any of that at all. — Diego, Nov 20 '20 at 18:30

Function that shifts columns written in JavaScript not working in Google sheets

1 Answers1