How to make the loop update the range automatically in App Script for Google Sheets

sunbshine 0
Viewed 37

Background of this project

I am working on the database of employee evaluation. The database look something similar to the mock table below.

Row KPI ID Evaluation type Owner(s) Other data
1 SomeUniqueKey1 Type A John WhatEver1
2 SomeUniqueKey2 Type B John, Jane, James WhatEver2
3 ... ... ... ...

So basically, I am trying to create the loop on this google sheet targeting 'Owner(s)' column that might contain a list-liked string that in this pattern ( a, b, c )(See row 2). My goal is to duplicate the row with multiple user and then modify the owner column to have only one user. So here is the end goal should look like:

Row KPI ID Evaluation type Owner(s) Other data
1 SomeUniqueKey1 Type A John WhatEver1
2 SomeUniqueKey2 Type B John WhatEver2
3 SomeUniqueKey2 Type B Jane WhatEver2
4 SomeUniqueKey2 Type B James WhatEver2
5 ... ... ... ...

What I have done so far

By checking whether each cell in the owner column have a "," or not (implying this is a list), if it contains ",", I convert it into an array and duplicate the row to the amount of length of the array minus 1 and later modify each row of that column into the order of and array. (Please see the code).

function myFunction() {
  var ws = SpreadsheetApp.openById("SHEET ID");
  var ss = ws.getSheetByName('SHEET NAME');
  var r = ss.getRange("A1:E20000");
  r.activate;
  var v = r.getValues();
  for (var i=1;i<=20000;i++){
    if(v[i-1][3].includes(",")){ 
      var temp = v[i-1][3].split(", ");
      ss.insertRows(i,temp.length-1);
      for (var j = 0; j<temp.length; j++ ){
        ss.getRange(i+temp.length-1,1,1,5).copyTo(ss.getRange(i+j,1,1,5));
        ss.getRange(i+j,3).setValue(temp[j]);
        }
        i = i+temp.length-1;
        }
    r.activate;
    v = r.getValues();
      }
      }

What being problematic

The loop stopped right after when they fix the first case of the issue. For instance, based on the mock table, the first case is on the row 2. After fixing the row 2, the later case after row 2 are not affected by the code anymore.

My assumption right now is that I insert the new row which change the referenced position. But I stuck here. Any kind of suggestions is appreciated.

google-sheets google-apps-script
1 Answers

When I saw your script, getValue, setValue, and copyTo are used in a loop. In this case, the process cost becomes high. Ref In your situation, I would like to propose the following flow.

  1. Retrieve values from Spreadsheet.
  2. Process the retrieved values and create an array.
  3. Put an array on Spreadsheet.

When this flow is reflected in a sample script, how about the following modified script?

Modified script:

function myFunction() {
  var ws = SpreadsheetApp.openById("SHEET ID");
  var ss = ws.getSheetByName('SHEET NAME');

  // I modified below script.
  var range = ss.getDataRange();
  var values = range.getValues().flatMap(r => {
    var idx = r.findIndex(c => c.includes(","));
    if (idx == -1) {
      return [r];
    }
    return r[idx].split(",").map(v => {
      var temp = r.slice();
      temp[idx] = v.trim();
      return temp;
    });
  });
  range.clearContent();
  ss.getRange(1, 1, values.length, values[0].length).setValues(values);
}

Or, if the column number is known, you might be able to be used.

function myFunction() {
  var ws = SpreadsheetApp.openById("SHEET ID");
  var ss = ws.getSheetByName('SHEET NAME');

  // I modified below script.
  var columnNumber = 3; // This is column "C".
  var range = ss.getDataRange();
  var values = range.getValues().flatMap(r => {
    if (r[columnNumber - 1].includes(",")) {
      return r[columnNumber - 1].split(",").map(v => {
        var temp = r.slice();
        temp[columnNumber - 1] = v.trim();
        return temp;
      });
    }
    return [r];
  });
  range.clearContent();
  ss.getRange(1, 1, values.length, values[0].length).setValues(values);
}
  • When this script is run, the values are retrieved from Spreadsheet and put the converted values to the same sheet.

References:

Tanaike0
Related