0

I am very new to coding and I am more familiar with python than javascript. I am able to copy the cells to the other tab but it deletes all the other values in those columns. How can I do it where it does not delete the data?

The script searches the destination sheet (Sheet1) in column A for a match with the source sheet (Assign) cell A2. Then it copies cells Assign!B2:E2 to the appropriate columns in the destination sheet (Sheet 1).
B2 to Col F, C2 to Col G, D2 to Col H and E2 to Col J. The problem is that I lose all the other data in those columns and I do not know why.

Source Sheet:
https://docs.google.com/spreadsheets/d/e/2PACX-1vRdzT0oASEX9c-p1PsTaNGZU5HvFf9IvyqrgKpnFmfOpQ-9Q4tY8xQFjO9lkB3cXSsQ4s7AC5RyENn9/pubhtml?gid=1281122410&single=true

enter image description here

Destination Sheet:
https://docs.google.com/spreadsheets/d/e/2PACX-1vRdzT0oASEX9c-p1PsTaNGZU5HvFf9IvyqrgKpnFmfOpQ-9Q4tY8xQFjO9lkB3cXSsQ4s7AC5RyENn9/pubhtml?gid=1224712314&single=true

enter image description here

function assign() {
 var ss =  SpreadsheetApp.getActiveSpreadsheet();
 var sheetA = ss.getSheetByName('Assign');
 var dataA = sheetA.getRange(1, 1, sheetA.getLastRow(), 5).getValues();
 var sheetB = ss.getSheetByName('Sheet1');
 var dataB = sheetB.getRange(1, 1, sheetB.getLastRow(), 1).getValues();
 var values = dataB.map(function(e) {
   var t = dataA.filter(function(f) {return f[0] == e[0]});
   return t.length > 0 ? [t[0][1]] : [""];
 });
 sheetB.getRange(1, 6, values.length, 1).setValues(values); // 6 means the column "F".
 var values = dataB.map(function(e) {
   var t = dataA.filter(function(f) {return f[0] == e[0]});
   return t.length > 0 ? [t[0][2]] : [""];
 });
 sheetB.getRange(1, 7, values.length, 1).setValues(values); // 7 means the column "G".
 var values = dataB.map(function(e) {
   var t = dataA.filter(function(f) {return f[0] == e[0]});
   return t.length > 0 ? [t[0][3]] : [""];
 });
 sheetB.getRange(1, 8, values.length, 1).setValues(values); // 8 means the column "H".
 var values = dataB.map(function(e) {
   var t = dataA.filter(function(f) {return f[0] == e[0]});
   return t.length > 0 ? [t[0][4]] : [""];
 });
 sheetB.getRange(1, 10, values.length, 1).setValues(values); // 10 means the column "J".
 }

`

1

1 Answer 1

0

Use Array.map(), like this:

function assign() {
  'use strict';
  const ss = SpreadsheetApp.getActive();
  const targetRange = ss.getRange('Sheet1!A1:J');
  const targetData = targetRange.getValues();
  const sourceData = ss.getRange('Assign!A1:E').getValues();
  const sourceKeys = sourceData.map(row => row[0]);
  const newData = targetData.map(row => {
    const key = row[0];
    const index = sourceKeys.indexOf(key);
    if (index === -1) {
      return row;
    }
    const newRow = sourceData[index];
    return [row[0], row[1], row[2], row[3], row[4], newRow[1], newRow[2], newRow[3], row[8], newRow[4]];
  });
  targetRange.setValues(newData);
}

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.