google-sheetsgoogle-apps-script

Combining scripts

I'm new to google scripts and I'm running into problems. Is there a way to combine these 2 scripts?

I've tried this, but have been getting error messages

function runBothScripts() {

    doSomethingFromScript1();
  randomize()
  const ss = SpreadsheetApp.getActive()
  const sheet = ss.getSheetByName('Sheet2')
  const originalValues = sheet.getRange('M5:M7').getValues()
  const randomizedValues = [...originalValues].sort((a, b) => 0.5 - Math.random())

  sheet.getRange('M9:M11').setValues(randomizedValues);

doSomethingFromScript2();
  randomize()
  const ss = SpreadsheetApp.getActive()
  const sheet = ss.getSheetByName('Sheet2')
  const originalValues = sheet.getRange('N5:N7').getValues()
  const randomizedValues = [...originalValues].sort((a, b) => 0.5 - Math.random())

  sheet.getRange('N9:N11').setValues(randomizedValues)

}`

I added a picture. The inputted data will be the names and the colors. The output will be randomized names and colors. Sorting of data will be specifically for each column (will not affect the other column)

Names Colors
a red
b green
c yellow
Solution

  • Apply Transposition Before Sorting Per Subarray

    To segregate the data per column, I recommend using a transpose method so that you may easily sort the data in row form. You may use the following:

    function runBothScripts() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('Sheet2');
  const originalValues = sheet.getRange('M5:N7').getValues(); //combine the ranges of both columns here
  var transposedValues = originalValues[0].map((col, i) => originalValues.map(row => row[i])); //transpose method to sort the columns easier as rows
  var randomizedValues = transposedValues.map(x=>x.sort((a, b) => 0.5 - Math.random()));
  var output = randomizedValues[0].map((col, i) => randomizedValues.map(row => row[i])); //transpose method to return to original position
  sheet.getRange('N9:M11').setValues(output); //combine the ranges of both output columns here
}

    For this, I used the following sample data:
    a red
    b green
    c yellow

    By transposing the data (script was adopted from this post), you can easily sort your data which is transformed from:

    [["a","red"], ["b","green"], ["c","yellow"]]

    to:

    [["a", "b", "c"], ["red", "green", "yellow"]]

    which can be sorted easily since the values are now grouped per subarray therefore isolating the column values. Afterwards, an additional transpose method is used to return the array to its original dimension.

    NOTE: Since this is a randomization of a smaller sample size, expect a higher chance of an output that is exactly the same as the input. Hence, if you do not want the current output, you might as well rerun the script.

    References