0
\$\begingroup\$

The link is to the example sheet of what I have done

This code solves the problem of shifting large amounts of specific data in a google spreadsheet. The data is shifted to the right by one column, but only the data containing the specified substring (in this case, "PO"). It is intended to shift the leftmost row over by one column.

https://docs.google.com/spreadsheets/d/12w4rGArGi1I1wlpm5yJJtT5AAlMM4LcZC31_DpP6jZQ/edit?usp=sharing

I shifted the rows that contain "PO" in my data selection. (see shift function)

It was suggested to me that I use array functions to made the code better, but I am honestly not sure how to refactor it. I am still very much a beginner.

I'm guessing I can more easily do this instead of trying to reassign array index values.

Here is my current script:

function shift() {
  try{
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var as = ss.getActiveSheet();                     
    var ar = as.getActiveRange();
    var vals = ar.getValues();
    var r; //variable for rows

    // in this first section, I've already stored the active range (my selection) into a two dimensional array.

    for (r = 0; r < vals.length; r++){                                           // for each row, up to the last row (iterate over all rows from top to bottom)
      if((vals[r][0].indexOf("PO") != -1)||(vals[r][0].indexOf("P0") != -1)){    // if first column in each row contains "PO"
        var c; // variable for columns                                                    
        var cols = []; // array to store all data temporarily (will be uses to set new values later)
        for (c = 0; c < vals[r].length; c++){                                   // then iterate over each column(cell) in the row
          if(c == 0){                                                           // if it is the first row,
            cols[c+1] = vals[r][c];                                             // assign second index of the array with the PO value (to simulate a shift)
            cols[c] = "";                                                       // assign the first index of the array a blank string
          }
          else{                                                                 // if it is not the first row
            cols[c+1] = vals[r][c];                                             // assign each additional column value to the next index (+1) of the array
          }
        }
        for (c = 0; c < vals[r].length; c++){                                   // once the array is finished, loop through the columns again foreach row
          vals[r][c] = cols[c];                                                 // this time, assigning the new values to the corresponding array indices
        }
      }
    }
    ar.setValues(vals);                                                         // now, set the values that you reassinged to the array
  }
  catch(err){
    SpreadsheetApp.getUi().alert(err);
  }
}
\$\endgroup\$
  • \$\begingroup\$ Welcome to Code Review! Your question is missing something important: what does your code do? As in, what problem does it solve? \$\endgroup\$ – Mast Oct 2 '18 at 14:25
  • \$\begingroup\$ Hello! I have added that into the description =) \$\endgroup\$ – Nikki Luzader Oct 2 '18 at 14:29
  • \$\begingroup\$ The site standard is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review - Asking Questions for guidance on writing good question titles. \$\endgroup\$ – BCdotWEB Oct 2 '18 at 14:35
  • \$\begingroup\$ I have modified the title to the standards, thanks for the info. \$\endgroup\$ – Nikki Luzader Oct 2 '18 at 14:40
1
\$\begingroup\$

Just as a general note, you could avoid the temporary array by copying the array from the end. I would also use a temporary variable for simplicity:

for (r = 0; r < vals.length; r++) {   
  var row = vals[r], first=row[0];
  if(first.indexOf("PO") != -1)||first.indexOf("P0") != -1)) {
    for (var c = row.length; c > 0' c--) {                                   
      row[c] = row[c-1];
    }
    row[0] = "";
  }
}

However in this case you can let JavaScript do all the work:

  if(first.indexOf("PO") != -1)||first.indexOf("P0") != -1)) {
    row.splice(0, 0, "");
  }

Also, assuming you only need to support recent browsers, I would look at using methods like forEach and includes.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.