SOLVED & UPDATED: Excel Online Script to Cut From One Sheet and Paste Into Another, from Random places in the Cut sheet

Question

I have totally re-written this since its seemed too difficult to explain, and by now Ive solved half of it anyway.

Now my problem is the same, but Ive made it simpler.

In short, I reserved 2 cells (A1 and A2) for a user to enter one number in each, which represent the top row and bottom row of a job, found somewhere in a group of over 100 jobs on a single sheet.

I need my script to read from 2 cells (A1 and A2) which will use those 2 numbers to go down the sheet of over 100 jobs (each job consist of only 15 rows) and copy one group of 15 rows (one job) over to another sheet in the same workbook for Archiving that job.

The difficulty is that this first sheet of 100 groups is a 'Moving' target. Every week other projects get inserted at the top so all the jobs get moved down, now that one job is 15 rows down further, or more.

I'm giving the user the option of finding the job they want to archive, and enter the first row of that job in A1, (which could be on row 200, for that day), then enter the row number at the bottom of that job in A2, (which could be at row 215, for that day).

One solution could be to just enter the top row number for that job, and have the program to add 15 to it for the bottom row to copy as a group. (I think the insert copied rows 'Record Action' that I did identified the top and bottom rows as a range, thats the only reason Im keeping both in the script, but dont know if its needed.)

The script will read cells A1 and A2 to know where to look (for that day), go copy that and paste it to the Archive sheet in the same workbook. Once this works, I will use the same method to go back and Remove Rows from that first sheet to clean up and remove that job since its now been Archived.

This is the current code that works, I replaced the one that didnt so you wont have to look further, but this same answer is below with fresher comments than here, so look for the comment here below dated Oct 26, 2023, 3:37 PM:

// Archive A Project

function main(workbook: ExcelScript.Workbook) {

  let selectedSheet = workbook.getActiveWorksheet();
  let archive = workbook.getWorksheet("Archive");

  //Assign the Archive worksheet to the ws variable
  let ws = workbook.getWorksheet("Archive");

  //Unprotect worksheet without password
  ws.getProtection().unprotect();

  let cell = selectedSheet.getRange("A1");
  let cell2 = selectedSheet.getRange("A2");

  // Read the value from the source cell
  let cellValue = cell.getValue();
  let cell2Value = cell2.getValue();

  // Store the   source cell value as a variable
  let myVariable = cellValue;
  let myVariable2 = cell2Value;
  let numRows = myVariable2 - myVariable + 1;
  let lastRow = 4 + numRows - 1;
  let dstAddress = "4:" + lastRow.toString();
  let srcAddress = myVariable.toString() + ":" + myVariable2.toString();

  archive.getRange(dstAddress).insert(ExcelScript.InsertShiftDirection.down);
  archive.getRange(dstAddress).copyFrom(selectedSheet.getRange(srcAddress));

  // Paste to range 4:19 on archive from range 4:19 on archive
  archive.getRange("4:19").copyFrom(archive.getRange("4:19"), ExcelScript.RangeCopyType.values, false, false);

  // Clear ExcelScript.ClearApplyTo.contents from range A1 on archive
  ////archive.getRange("A1").clear(ExcelScript.ClearApplyTo.contents);

  //Relocking the Archive sheet
  //Unprotect worksheet without password
  ws.getProtection().protect();

  // Go to Archive to Confirm
  workbook.getWorksheet("Archive").activate();
}

Answer 1

SOLVED AND WORKING!!

This is a solution I thought of to grab some rows at any point in a long list, and copy those over to another sheet I use to Archive older projects, and it works great.

I use basically the same thing in another script to then delete the project from the original sheet after its been copied over to the Archive.

There is no prompt, but the script reads from two pre-determined cells, A1 and A2, where the user enters what that range is, (top of project and bottom of project, the project is always 16 rows), so the two numbers are always 16 in range.

The sheet with over 100 projects, each 16 rows, so there can be 1,600 plus rows in this one sheet, and I can grab any project in that list, by just entering the top row of that one project, and the bottom row in that long list,

example: I enter 575 in cell A1, and 590 in cell A2.

The script knows to look for those 2 cells (A1 and A2), reads the numbers 575 and 590, turns that into a string range, copies that range and pastes it into the Archive sheet.

The Archive sheet is Locked, but this code also unlocks that sheet, pastes it, re-locks the sheet back, then goes to that sheet so the user can confirm the project got copies over correctly.

Then it clears those values from cells A1 and A2 after its done so it doesn't get accidentally copied or deleted if someone hits the script button by mistake.

I have over 10 engineers using these scripts, with their own sheets, each engineer can have over 100 jobs, so this works from within any sheet, and always copies the older jobs to the top of the Archive sheet in rows 4:19, which bumps down any existing archived jobs.

Hope this helps others its really solved my problem.

Here is the code:

// Archive A Project

function main(workbook: ExcelScript.Workbook) {

  let selectedSheet = workbook.getActiveWorksheet();
  let archive = workbook.getWorksheet("Archive");

  //Assign the Archive worksheet to the ws variable
  let ws = workbook.getWorksheet("Archive");

  //Unprotect worksheet without password
  ws.getProtection().unprotect();

  let cell = selectedSheet.getRange("A1");
  let cell2 = selectedSheet.getRange("A2");

  // Read the value from the source cell
  let cellValue = cell.getValue();
  let cell2Value = cell2.getValue();

  // Store the   source cell value as a variable
  let myVariable = cellValue;
  let myVariable2 = cell2Value;
  let numRows = myVariable2 - myVariable + 1;
  let lastRow = 4 + numRows - 1;
  let dstAddress = "4:" + lastRow.toString();
  let srcAddress = myVariable.toString() + ":" + myVariable2.toString();

  archive.getRange(dstAddress).insert(ExcelScript.InsertShiftDirection.down);
  archive.getRange(dstAddress).copyFrom(selectedSheet.getRange(srcAddress));

  // Paste to range 4:19 on archive from range 4:19 on archive
  archive.getRange("4:19").copyFrom(archive.getRange("4:19"), ExcelScript.RangeCopyType.values, false, false);

  // Clear ExcelScript.ClearApplyTo.contents from range A1 on archive
  ////archive.getRange("A1").clear(ExcelScript.ClearApplyTo.contents);

  //Relocking the Archive sheet
  //Unprotect worksheet without password
  ws.getProtection().protect();

  // Go to Archive to Confirm
  workbook.getWorksheet("Archive").activate();
}

Answer 2

make a pop-up prompt

https://developer.mozilla.org/en-US/docs/Web/API/Window/prompt

let sign = prompt("What's your sign?");

if (sign.toLowerCase() === "scorpio") {

alert("Wow! I'm a Scorpio too!");

}

other questions about two or more workbooks,not very efficient with script because you should open them first and then perform cut and paste.

Answer 3

The Office Scripts button currently doesn't support prompting/passing parameters, yet. It's in our backlog, though. Please feel free to share your feedback: https://feedbackportal.microsoft.com/feedback/forum/c23f3b77-f01b-ec11-b6e7-0022481f8472. Your voice can help us better prioritize new features in our backlog.

Unfortunately, JavaScript's built-in functions like alert or prompt won't work in Office Scripts. Office Scripts is designed to work for both "attended" (running in the browser) and "unattended" (running in Power Automate). Having UI interactions like alert and prompt in the script will break the unattended scenario because user interactions are not possible when the script is running in the cloud.

As a workaround, one possible but not ideal solution is to have different scripts for each individual engineer. The main part of each script can be the same and the only difference would probably be where in the workbook the script needs to operate on. You can then create a button for each script on each engineer's respective worksheet.

I know this solution looks very bad due to all the almost identical but duplicate script copies for each engineer, and the concern of maintainability (imagine when you need to change the logic of the script, you'll need to go through all the script copies and repeat the same thing). But I cannot think of a better way of doing this. I'm open to other ideas. Also regarding the maintainability, since Office Scripts are stored on your OneDrive for Business as a *.osts files (under the Documents folder), potentially you can probably write up some automated process (e.g., through Power Automate) to batch update the scripts. But that may require some extra thinking and efforts.

Thoughts?

Answer 4

I typed up a response and its not showing, so I'll type it again, argh.

Thanks Yutao,

its a good idea, thanks for the input.

What Im mainly needing, (which you may have answered but I didnt catch) was a way for the engineer to enter a job number and the script to go find that on the dashboard tab, and also in their main sheet (tab) and then archive that into the Archive tab.

There might be over 100 jobs from different engineers in the dashboard tab, and the script needs to be able to find that by job number.

I agree with you saying it could be tedious to make so many scripts, but fortunately Ive already done that, a script for each engineer, all the same except I made each engineers script (with a button on their own sheet) to enter their name on their job from the template tab, so now I just needed 1 template that puts the job in their folder that actual work is done one, and a similar copy of the template goes to the dashboard which only pulls from their master in their tab. So all the jobs from each engineer can be viewed on one place in the dashboard.

But my boss didnt want any hands on by the engineer to Archive stuff, just a button to do it. So now I need to find a way for the script to find the needle in the haystack dashboard by job number, Thus the prompt or some way for it to know what job to search for since its constantly moving down as other jobs get inserted. (hitting a moving target).

Thanks Yutao for the idea.

Answer 5

Thanks Yutao,

You are mostly correct, but the job number gets assigned on the fly. When a customer comes in, a job number is assigned then. So that when the engineer clicks their button, it creates the two job forms from the template sheet.

The engineer then enters that job number on their sheet, which then the dashboard sheet pulls all data from the engineer sheet to populate it, including the job number.

So hard coding it in manually would be a chore I would not want to tackle every time, since there will be close to a hundred jobs found on the dashboard sheet, and a chance of my typo's could grab another job that is not ready for Archiving.

I suppose I could do that, but retirement is close, and I dont think anyone would want to take on a project that could be automated if only a way to make the script Prompt for user input of the job number, or a fixed cell location on their sheet (say A1) where the script can read a value the engineer enters at that cell the script can read, and use that value to find the job on the dashboard and their own sheet.

I appreciate your creative thinking.