Is there a way to list the array values in one cell by adding one onto another-CodePudding

I'm making a google sheets app function that checks if the ID in one sheet can be associated with any of the patients (each patient receives an ID), then add it into their file (a single cell next to their name).

I'm at a point where I can get the info into the cell with .copyValuesToRange, but the problem is that all the values are copied into the cell one after another. The desired effect is that I get all values separated by ", ".

Here's my code:

function newCaseIn() {
  let app = SpreadsheetApp;
  let dest = app.getActiveSpreadsheet().getSheetByName("Baza Danych");
  let form = app.getActiveSpreadsheet().getSheetByName("Zgloszenia");

  
  for (let i = 2; i < 200; i  ) {
    if (form.getRange(i, 2).getValue()) {
      while (true) {
        form.getRange(i, 3).copyValuesToRange(0, 9, 9, 2, 2);
        
      }
    }
  }
}

And here's how the database looks: Database FormSubmissions

NOTE: There is a form that comes down to the second sheet to allow people submit new patient files to a specified ID

CodePudding user response：

It could be something like this:

function main() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let dest = ss.getSheetByName("Baza Danych");
  let form = ss.getSheetByName("Zgloszenia");

  // get all data from the form
  var source_data = form.getDataRange().getValues();
  source_data.shift(); // remove the header

  // make the data object
  // in: 2d array [[date,id1,doc], [date,id2,doc], ...]
  // out: object {id1: [doc, doc, doc], id2: [doc, doc], ...}
  var source_obj = {};
  while(source_data.length) {
    let [date, id, doc] = source_data.shift();
    try { source_obj[id].push(doc) } catch(e) { source_obj[id] = [doc] }
  }

  // get all data from the dest sheet
  var dest_data = dest.getDataRange().getValues();

  // make a new table from the dest data and the object
  var table = [];
  while (dest_data.length) {
    let row = dest_data.shift();
    let id = row[0];
    let docs = source_obj[id]; // get docs from the object
    if (docs) row[8] = docs.join(', ');
    table.push(row);
  } 

  // clear the dest sheet and put the new table
  dest.clearContents();
  dest.getRange(1,1,table.length,table[0].length).setValues(table);
}

Update

The code from above clears existed docs in the cells of column 9 and fills it with docs from the form sheet (for relevant IDs).

If the dest sheet already has some docs in the column 9 and you want to add new docs you have to change the last loop this way:

  // make a new table from the dest data and the object
  var table = [];
  while (dest_data.length) {
    let row = dest_data.shift();
    let id = row[0];
    let docs = source_obj[id]; // get docs from the object
    if (docs) {
      let old_docs = row[8];
      row[8] = docs.join(', ');
      if (old_docs != '') row[8] = old_docs   ', '   row[8];
    }
    table.push(row);
  }