CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptdata-transfer
Jesse Bridges
Jesse Bridges

Reputation: 23

Transfer Data from one sheet to another

I'm hoping someone will lend me a hand with a writing a Google Apps Script to be used on Google Sheets (unless it can be done with a standard formula string).

I would like a script to Search for a specific "text value" in a specified column of "Sheet1", if "text value" is found, data from that "Row" is transferred to "Sheet2" in the First available empty Row. Then, after the data is transferred to "Sheet2", the row with data from "Sheet1" is deleted.

*Note, "Sheet2" has fewer Headers than "Sheet1". I only want Data from "sheet1" that has the same Headers as "sheet2" Here is a sample workbook Data Transfer.

If "text" in column F is "lost", transfer data from that row into "sheet2" but ONLY data relevant to headers in "sheet2".

Upvotes: 1

Views: 1286

Answers (1)

Cooper
Cooper

Reputation: 64140

Find, Move and Delete

txt is the text to search for

col is the column name to search in

function findMoveDelete(txt,col) {
  var txt=txt||'Hoover';//search text
  var col=col||'Location';//column name
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  var dsh=ss.getSheetByName('Sheet2');
  var dHdrToIdx={dhA:[]};
  dHdrToIdx.dhA=dsh.getRange(1,1,1,dsh.getLastColumn()).getValues()[0];
  dHdrToIdx.dhA.forEach(function(hdr,i){dHdrToIdx[hdr]=i;});
  var rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
  var sHdrToIdx={shA:[]};
  sHdrToIdx.shA=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
  sHdrToIdx.shA.forEach(function(hdr,i){sHdrToIdx[hdr]=i;});
  var vA=rg.getValues();
  var d=0;
  for(var i=0;i<vA.length;i++) {
    //Find
    if(vA[i][sHdrToIdx[col]]==txt) {
      var row=[];
      dHdrToIdx.dhA.forEach(function(dhdr,j){
        row.push(vA[i][sHdrToIdx[dHdrToIdx.dhA[j]]]);
      });
      dsh.appendRow(row);//Move
      sh.deleteRow(i+2-d++);//Delete
    }
  }
}

Sheets 1 and 2 after run:

enter image description here

enter image description here

You must have the same headers in sheet1 and sheet2. You can put them in any order you like but they must be the same in both sheets.

Upvotes: 1

Related Questions