8. Moving and copying lines

Use Case Details for Move and Copy Rows

A typical case of using line movement is"archive"and the distribution of rows to individual sheets"When managing records on multiple sheets".

A typical case of using row copy is the above "When managing records on multiple sheets",If you want to keep a master sheet with all the information.

For a slightly more advanced use of line copying use "When saving the updater and update date/time"there is.When a row's status changes, itstake a logCases may be considered.

Archive

Work faster andArchivecan.

For example, if you manage branch office activity records on a sheet, and if the fiscal year changes, if you do not use the sheet in a consistent manner, the same sheet will be indented to record information for the past fiscal year. It will not be possible to use the system in a unified manner throughout the company, for example, because some branch offices create a new sheet every fiscal year.

You can avoid this confusion by setting up your workflow to move rows from previous years to a separate sheet for archiving each time the year changes. (It is also possible to configure settings such as archiving rows after half a year in order to handle work that spans multiple fiscal years.)

In the video below, the to-do list archives the done list.

Introductory video for task list archiving

When you need to manage records on multiple sheets

workflow.The line movement should be done by the teamFocus on the most relevant work itemsmake it possible.

For example:

1. A procurement request is sent to the sheet via a form.
2. Add request details and assign the request to your IT team.
3. IT manager approves the request.
4. Rows containing procurement requests are automatically moved to your current IT project sheet, awaiting your next action.

Video showing an example of sorting IT requests into sheets for each category

If sharing the master sheet poses security concerns

When sharing by sheet, if there is a security problem when sharing to the master sheet, by copying to an individual sheet according to a certain standard, the user who received the sharing of the individual sheet can access only that sheet. , without giving access to other information in the master sheet.

If you want the report to show only those rows to the logged-in user, you'll need to share the original sheet after all, so you'll need to use something like a dashboard for this purpose. (→Make it possible to view specific information without sharing the original sheet by publishing the dashboard and report) line copy method does not have that problem.

Save the data of updater and date of update

Modified By and Modified Time data will be changed every time the row is changed.If you look at the history of those columns, you can access past updaters and update date/time data, but cell history cannot be referenced by automation or functions.

By doing a copy of the line,On the copy destination sheet, the updated by and updated date data are retained as the creator and creation dateSo you don't have to worry about rewriting the data when you update that row.

when logging

As for when to "copy the row to save the updater, update date and time data", it can be used, for example, when taking a log.

For example, let's say that we are creating an attendance app, and we want to change the statuses of Attendance, Leaving, Returning, and Leaving in the same row and column using a drop-down list.

Whenever this status changes, you can make a copy of the row to keep track of when those statuses happened.In this case, it's a good idea to give the row an ID.Relying on this ID, if you get the date and time information of the work attendance, middle school, return, and leave of the same ID row, for example, you can find the working time by the time you leave work - the time you come to work - (the time you return to work - the time you return to work). I can do it.

Workflow is moved, created on the original sheet side of the copy

Always create and edit workflows on the source sheet.You can add actions to existing automated workflows or launch new workflows.

You can add it to a workflow or create a new workflow from scratch.

Trigger selection

Give the workflow a name and specify when it should run, such as on a specific date or when specific information in a row changes.

Cells containing formulas or cell links that span multiple sheets cannot be used as triggers

To prevent an infinite approval loopFormulas that span multiple sheetsor sending us a message onCell linkCells that contain do not trigger automated actions that automatically modify the sheet (move rows, copy rows, lock rows, unlock rows, request approval).To avoid thisTime-based automationor sending us a message onPeriodicConsider using a workflow.

Setting conditions

If you want the workflow to run only in certain cases after selecting a trigger, you have the option to specify conditions.

Then select Move Row or Copy Row from the list of actions.

Both the source sheet and the sheet to be moved/copied to require transfer of administrative rights

To move and copy rows, you must have admin privileges on the destination sheet.Multiple operations can be easily combined in an automated workflow.

Combination with other actions

You can even combine multiple actions in the same automated workflow.For example, once a row on a sheet has been approved with a Request Approval action, you can move or copy that row to an archive sheet with a Move Row or Copy Row action.

Automatically move or copy rows between sheets

"line movement"Or"line copy' action allows you to automatically move or copy rows to another sheet if a condition is met.For example:

---

Things to know about moving rows between sheets and copying rows

Keep the following in mind when moving or copying rows between sheets:

Cell history is preserved in case of move, not preserved in copy destination in case of copy

• When moving rows between sheets:
  • The moved rows are removed from the original sheet and added to the destination sheet.
  • Row cell history is still available.
• When copying rows between sheets:
  • Copied rows are added to the destination sheet instead of being deleted from the original sheet.
  • Cell historyis not retained on the destination sheet.

Avoid security risks to cell history with row copying

Cloudsmart has verified the history of the cell, and it can be viewed even by users with unlicensed viewing rights only.This is a security risk if the past history should not be visible to the parties involved.

One way around this is to use line copying.

As you can see below, in the case of row copy, cell history is not preserved in the copy destination sheet, so if you do not want to show the viewing authority to users who do not want to see the view permission, you can share the copy destination sheet to avoid this security issue. Concerns are removed.

Other considerations

• The moved or copied rows are at the bottom of the target sheet and can be selected and dragged to another location.
• Row attachments/links and comments are automatically moved to the target sheet.
• Row formulas are replaced with static values ​​in the target sheet.
• If the source sheet contains columns that are not in the target sheet, such columns are automatically created in the target sheet when moving/copying rows.

When moving or copying rows containing system or baseline columns

• If the column exists in the target sheet but not in the source sheet,[Author] The column contains the date/time the row was moved or copied.
• [Author] The column shows who moved or copied the row.If the row was moved or copied by automation,automation@smartsheet.com It will be displayed.
• If the column exists in both the target sheet and the source sheet, the value of the source sheet is carried over to the target sheet.Unlike other column types, column names do not have to match.

Errors when moving or copying system columns

System columnor sending us a message onbaseline columnIf the names match columns with different column types (dropdown, string/number, etc.), the following error can occur:

A row cannot be added to the target sheet because the column name is the same as a system column in the original sheet.

A row cannot be added to the target sheet because the system column name is the same as the column in the original sheet.

How to resolve errors when moving or copying system columns

To resolve this, it is necessary to resolve the move and copy rule violations on system columns.

• If either needs to have another type of column with the same intent as the system column
  • Renaming one of the offending system columns, either source or destination,
    • For example, in the original sheetCreated By (system column), to the destination, for example, to the effect that the creator of the work,Creator (Contact)If you want to have a column ofCreator of work (contact)The error goes away if you change it to
• "Column name same as system column"to be retained in the original sheet and the destination sheet
  • Make the column types match the original sheet
    • For example, the original sheet (Created By, System Column) vs Move sheet (Author, contact, dropdown list, etc.), change the column type of the destination sheet toCreated By (system column)fix to

Modified by, modified date and time are moved, copy destination retains its own modified data

As a result of the test, regarding the update date and time and the updater, it behaves to retain its own update data at the destination as follows.

• When moving or copying
  • The update date and time are the same on both sides
  • Updaters are automated on the go (automation@smartsheet.com)
• When updating independently at the destination
  • Update data at the destination is recorded
    • The update date and time is the update date and time at the update destination
    • The updater ceases to be an automation (automation@smartsheet.com) if done by a human. (e.g. someone@clodsmart.jp).
  • Note that simply changing the cell color does not change the update information.