Styjun

Can an escape pod land on Earth from orbit and not be immediately detected?

SQL Server has encountered occurences of I/O requests taking longer than 15 seconds

Threading data on TimeSeries

When is the phrase "j'ai bon" used?

Does PC weight have a mechanical effect?

A Tale of Snake and Coffee

Print the phrase "And she said, 'But that's his.'" using only the alphabet

Can a 40amp breaker be used safely and without issue with a 40amp device on 6AWG wire?

Can artificial satellite positions affect tides?

Fastest path on a snakes and ladders board

Manager wants to hire me; HR does not. How to proceed?

Someone who is granted access to information but not expected to read it

Sakkāya-Ditthi and Self-View

Background for black and white chart

How can Caller ID be faked?

Is there a term for someone whose preferred policies are a mix of Left and Right?

How did the European Union reach the figure of 3% as a maximum allowed deficit?

Leveling up and Getting Items!

Struggling to present results from long papers in short time slots

How did Avada Kedavra get its name?

Why doesn't Mathematica completely draw the fit?

Arcane Tradition and Cost Efficiency: Learn spells on level-up, or learn them from scrolls/spellbooks?

I sent an angry e-mail to my interviewers about a conflict at my home institution. Could this affect my application?

Does WiFi affect the quality of images downloaded from the internet?

How to move some data to another tab based on specific column criteria

Copy every row of Data from one sheet that has a given value in column K to another sheetCopy a column with a formula to another tabRetrieve row from a table (with a specific value) and insert specific row to a new sheetHow to apply functions/formulas from last row after inserting a new row to google sheet?Moving Row of Data from One Sheet to Another - GoogleSheetScript to copy data from one sheet to another based on criteriaFetch rows with column matching a criteria and send them by email (Google Sheets)How do i move or copy data from google Sheet1 to Sheet2 without deleting the copied data when the original data is being deletedGoogle Script .getvalue() Not Working With Cells With a Formula In ItGoogle script equivalent for Excel script to last column in row offset by one

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

Im currently using this code to move some rows if Column 6 contains "NA"

function MoveNA() 

 var s, targetSheet, found;
 var s = SpreadsheetApp.getActive();
 var allsheets = s.getSheets();
 for(var s in allsheets) 
 (s.getName() == "Inventory") 
 ) continue;

 targetSheet = SpreadsheetApp.getActive()
 .getSheetByName("NA"),

 found = 0,
 s.getDataRange()
 .offset(1, 0)
 .getValues()
 .forEach(function (r, i) 
 if (r[5] == 'NA') 
 sourceRange = s.getRange((i + 2) - found, 1, 1, s.getLastColumn());
 targetSheet.appendRow(sourceRange.getDisplayValues()[0])
 s.deleteRow((i + 2) - found);
 found += 1;
 SetFormulasNA();
 
 )

I don't know how to update the code in order to move only the data in Columns: B, K,L,M,N,O,P

I want to copy as Values, not the formulas

All other data in the source row I don't want to move

Any help please ?

google-apps-script

share|improve this question

asked Mar 25 at 2:39

SATH59SATH59

579

add a comment | 

0

Im currently using this code to move some rows if Column 6 contains "NA"

function MoveNA() 

 var s, targetSheet, found;
 var s = SpreadsheetApp.getActive();
 var allsheets = s.getSheets();
 for(var s in allsheets) 
 (s.getName() == "Inventory") 
 ) continue;

 targetSheet = SpreadsheetApp.getActive()
 .getSheetByName("NA"),

 found = 0,
 s.getDataRange()
 .offset(1, 0)
 .getValues()
 .forEach(function (r, i) 
 if (r[5] == 'NA') 
 sourceRange = s.getRange((i + 2) - found, 1, 1, s.getLastColumn());
 targetSheet.appendRow(sourceRange.getDisplayValues()[0])
 s.deleteRow((i + 2) - found);
 found += 1;
 SetFormulasNA();
 
 )

I don't know how to update the code in order to move only the data in Columns: B, K,L,M,N,O,P

I want to copy as Values, not the formulas

All other data in the source row I don't want to move

Any help please ?

google-apps-script

share|improve this question

asked Mar 25 at 2:39

SATH59SATH59

579

add a comment | 

Im currently using this code to move some rows if Column 6 contains "NA"

function MoveNA() 

 var s, targetSheet, found;
 var s = SpreadsheetApp.getActive();
 var allsheets = s.getSheets();
 for(var s in allsheets) 
 (s.getName() == "Inventory") 
 ) continue;

 targetSheet = SpreadsheetApp.getActive()
 .getSheetByName("NA"),

 found = 0,
 s.getDataRange()
 .offset(1, 0)
 .getValues()
 .forEach(function (r, i) 
 if (r[5] == 'NA') 
 sourceRange = s.getRange((i + 2) - found, 1, 1, s.getLastColumn());
 targetSheet.appendRow(sourceRange.getDisplayValues()[0])
 s.deleteRow((i + 2) - found);
 found += 1;
 SetFormulasNA();
 
 )

I don't know how to update the code in order to move only the data in Columns: B, K,L,M,N,O,P

I want to copy as Values, not the formulas

All other data in the source row I don't want to move

Any help please ?

google-apps-script

share|improve this question

asked Mar 25 at 2:39

SATH59SATH59

579

function MoveNA() 

 var s, targetSheet, found;
 var s = SpreadsheetApp.getActive();
 var allsheets = s.getSheets();
 for(var s in allsheets) 
 (s.getName() == "Inventory") 
 ) continue;

 targetSheet = SpreadsheetApp.getActive()
 .getSheetByName("NA"),

 found = 0,
 s.getDataRange()
 .offset(1, 0)
 .getValues()
 .forEach(function (r, i) 
 if (r[5] == 'NA') 
 sourceRange = s.getRange((i + 2) - found, 1, 1, s.getLastColumn());
 targetSheet.appendRow(sourceRange.getDisplayValues()[0])
 s.deleteRow((i + 2) - found);
 found += 1;
 SetFormulasNA();
 
 )

share|improve this question

asked Mar 25 at 2:39

SATH59SATH59

579

share|improve this question

asked Mar 25 at 2:39

SATH59SATH59

579

asked Mar 25 at 2:39

SATH59SATH59

579

asked Mar 25 at 2:39

SATH59SATH59

579

1 Answer
1

active

oldest

votes

0

I updated your code to include cols you want to copy values from. See dataColumns array. It is a 1 based array, meaning, col B is 2, and so on. This only copies values into destination sheet and doesn't delete rows from source sheet.

function MoveNA() 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = ss.getSheets();

 // destination sheet
 var desSheet = ss.getSheetByName('NA');

 // sheets to exclude
 var excludes = ['Search', 'xx', 'NA', 'Inventory'];

 // which cols to copy values from
 var dataColumns = [2, 11, 12, 13, 14, 15, 16];

 // copy values
 sheets.forEach(function(sheet) 
 // check sheet is not in excludes
 if (excludes.indexOf(sheet.getName()) != -1) return;

 var found = 0;
 sheet
 .getDataRange()
 .getValues()
 .forEach(function(row, i) 
 // check condition
 if (row[5] == 'NA') 
 row.forEach(function(val, j) 
 if (dataColumns.indexOf(j + 1) != -1) return;
 row[j] = '';
 );
 desSheet.appendRow(row);
 // delete source row
 // sheet.deleteRow(i + 1 - found);
 // found += 1;
 
 );
 );

share|improve this answer

edited Mar 27 at 9:21

answered Mar 25 at 5:19

ra89fira89fi

979168

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Im getting The coordinates or dimensions of the range are invalid. (line 34, file "Move to NA")
  
  – SATH59
  Mar 25 at 6:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  and I DO need delete rows from source sheet. once is moved pls
  
  – SATH59
  Mar 25 at 6:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  test new code, if it works, enable delete code by uncommenting
  
  – ra89fi
  Mar 25 at 7:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm still getting The coordinates or dimensions of the range are invalid. (line 38)
  
  – SATH59
  Mar 25 at 8:53
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  strange. try again now
  
  – ra89fi
  Mar 25 at 9:48
  

  
  
  
  

 | 
show 11 more comments

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55330608%2fhow-to-move-some-data-to-another-tab-based-on-specific-column-criteria%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

I updated your code to include cols you want to copy values from. See dataColumns array. It is a 1 based array, meaning, col B is 2, and so on. This only copies values into destination sheet and doesn't delete rows from source sheet.

function MoveNA() 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = ss.getSheets();

 // destination sheet
 var desSheet = ss.getSheetByName('NA');

 // sheets to exclude
 var excludes = ['Search', 'xx', 'NA', 'Inventory'];

 // which cols to copy values from
 var dataColumns = [2, 11, 12, 13, 14, 15, 16];

 // copy values
 sheets.forEach(function(sheet) 
 // check sheet is not in excludes
 if (excludes.indexOf(sheet.getName()) != -1) return;

 var found = 0;
 sheet
 .getDataRange()
 .getValues()
 .forEach(function(row, i) 
 // check condition
 if (row[5] == 'NA') 
 row.forEach(function(val, j) 
 if (dataColumns.indexOf(j + 1) != -1) return;
 row[j] = '';
 );
 desSheet.appendRow(row);
 // delete source row
 // sheet.deleteRow(i + 1 - found);
 // found += 1;
 
 );
 );

share|improve this answer

edited Mar 27 at 9:21

answered Mar 25 at 5:19

ra89fira89fi

979168

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Im getting The coordinates or dimensions of the range are invalid. (line 34, file "Move to NA")
  
  – SATH59
  Mar 25 at 6:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  and I DO need delete rows from source sheet. once is moved pls
  
  – SATH59
  Mar 25 at 6:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  test new code, if it works, enable delete code by uncommenting
  
  – ra89fi
  Mar 25 at 7:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm still getting The coordinates or dimensions of the range are invalid. (line 38)
  
  – SATH59
  Mar 25 at 8:53
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  strange. try again now
  
  – ra89fi
  Mar 25 at 9:48
  

  
  
  
  

 | 
show 11 more comments

0

I updated your code to include cols you want to copy values from. See dataColumns array. It is a 1 based array, meaning, col B is 2, and so on. This only copies values into destination sheet and doesn't delete rows from source sheet.

function MoveNA() 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = ss.getSheets();

 // destination sheet
 var desSheet = ss.getSheetByName('NA');

 // sheets to exclude
 var excludes = ['Search', 'xx', 'NA', 'Inventory'];

 // which cols to copy values from
 var dataColumns = [2, 11, 12, 13, 14, 15, 16];

 // copy values
 sheets.forEach(function(sheet) 
 // check sheet is not in excludes
 if (excludes.indexOf(sheet.getName()) != -1) return;

 var found = 0;
 sheet
 .getDataRange()
 .getValues()
 .forEach(function(row, i) 
 // check condition
 if (row[5] == 'NA') 
 row.forEach(function(val, j) 
 if (dataColumns.indexOf(j + 1) != -1) return;
 row[j] = '';
 );
 desSheet.appendRow(row);
 // delete source row
 // sheet.deleteRow(i + 1 - found);
 // found += 1;
 
 );
 );

share|improve this answer

edited Mar 27 at 9:21

answered Mar 25 at 5:19

ra89fira89fi

979168

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Im getting The coordinates or dimensions of the range are invalid. (line 34, file "Move to NA")
  
  – SATH59
  Mar 25 at 6:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  and I DO need delete rows from source sheet. once is moved pls
  
  – SATH59
  Mar 25 at 6:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  test new code, if it works, enable delete code by uncommenting
  
  – ra89fi
  Mar 25 at 7:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm still getting The coordinates or dimensions of the range are invalid. (line 38)
  
  – SATH59
  Mar 25 at 8:53
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  strange. try again now
  
  – ra89fi
  Mar 25 at 9:48
  

  
  
  
  

 | 
show 11 more comments

I updated your code to include cols you want to copy values from. See dataColumns array. It is a 1 based array, meaning, col B is 2, and so on. This only copies values into destination sheet and doesn't delete rows from source sheet.

function MoveNA() 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = ss.getSheets();

 // destination sheet
 var desSheet = ss.getSheetByName('NA');

 // sheets to exclude
 var excludes = ['Search', 'xx', 'NA', 'Inventory'];

 // which cols to copy values from
 var dataColumns = [2, 11, 12, 13, 14, 15, 16];

 // copy values
 sheets.forEach(function(sheet) 
 // check sheet is not in excludes
 if (excludes.indexOf(sheet.getName()) != -1) return;

 var found = 0;
 sheet
 .getDataRange()
 .getValues()
 .forEach(function(row, i) 
 // check condition
 if (row[5] == 'NA') 
 row.forEach(function(val, j) 
 if (dataColumns.indexOf(j + 1) != -1) return;
 row[j] = '';
 );
 desSheet.appendRow(row);
 // delete source row
 // sheet.deleteRow(i + 1 - found);
 // found += 1;
 
 );
 );

share|improve this answer

edited Mar 27 at 9:21

answered Mar 25 at 5:19

ra89fira89fi

979168

function MoveNA() 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = ss.getSheets();

 // destination sheet
 var desSheet = ss.getSheetByName('NA');

 // sheets to exclude
 var excludes = ['Search', 'xx', 'NA', 'Inventory'];

 // which cols to copy values from
 var dataColumns = [2, 11, 12, 13, 14, 15, 16];

 // copy values
 sheets.forEach(function(sheet) 
 // check sheet is not in excludes
 if (excludes.indexOf(sheet.getName()) != -1) return;

 var found = 0;
 sheet
 .getDataRange()
 .getValues()
 .forEach(function(row, i) 
 // check condition
 if (row[5] == 'NA') 
 row.forEach(function(val, j) 
 if (dataColumns.indexOf(j + 1) != -1) return;
 row[j] = '';
 );
 desSheet.appendRow(row);
 // delete source row
 // sheet.deleteRow(i + 1 - found);
 // found += 1;
 
 );
 );

share|improve this answer

edited Mar 27 at 9:21

answered Mar 25 at 5:19

ra89fira89fi

979168

answered Mar 25 at 5:19

ra89fira89fi

979168

answered Mar 25 at 5:19

ra89fira89fi

979168