office script in excel on web

Question

office script in excel on web

Phillip Saulnier 20

I have a problem with my excel web worksheet

I have a sheet with the information on it

I have set the script to unhide the test sheet then hide the information sheet

After the test is finished there is a submit script to submit the result of the test, reopen the information sheet and hide the test sheet(for the next person to start test)

The first time it ran as suspected

After the scripted buttons work like set, but the information being entered on the test sheet was population the correct cells but on the hidden information sheet instead of the test sheet.

Tanay Prasad 2,250 Reputation points

2023-06-28T11:46:27.91+00:00

I am not very clear on the issue.

Do you mind explaining in detail?
Phillip Saulnier 20 Reputation points

2023-06-28T18:53:31.7933333+00:00

i have created a test on a sheet called test and created a script button so another person could submit their answer and return to the sheet called information so the next person can do the test. i also created a script button on the information sheet to take the user to the test sheet so they can do the test, both button hide the sheet they are on. the first time i ran it it worked,but after every time i ran the script to goto the test the test sheet would show but any time i tried to enter data on it it would save the data on the information sheet which was hidden
Tanay Prasad 2,250 Reputation points

2023-06-29T06:42:02.73+00:00

Seems like there might be an issue with how you are referencing the sheets in your script.

Try creating a more simplified version of your worksheet that contains only the essential components (such as two sheets, buttons, and simple data entry). Test the script with this simple configuration to narrow down the problem.

Phillip Saulnier 20

this is the first script that I run on sheet named INFORMATION 
this unhides sheet test
clears the answer cells D18:D27
and hides the INFORMATION sheet

function main(workbook: ExcelScript.Workbook) {
  let tEST = workbook.getWorksheet("TEST");
  // Set sheet visibility to visible
  tEST.setVisibility(ExcelScript.SheetVisibility.visible);
  // Clear ExcelScript.ClearApplyTo.contents from range D18:D27 on tEST
  tEST.getRange("D18:D27").clear(ExcelScript.ClearApplyTo.contents);
  let selectedSheet = workbook.getActiveWorksheet();
  // Set sheet visibility to hidden
  selectedSheet.setVisibility(ExcelScript.SheetVisibility.hidden);
}

This is the 2nd script that I run on the sheet named test 
This unhides sheet INFORMATION
Clears the cell for user name B15
Hides the TEST sheet
function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  // Clear ExcelScript.ClearApplyTo.contents from range B15 on selectedSheet
  selectedSheet.getRange("B15").clear(ExcelScript.ClearApplyTo.contents);
  let iNFORMATION = workbook.getWorksheet("INFORMATION");
  // Set sheet visibility to visible
  iNFORMATION.setVisibility(ExcelScript.SheetVisibility.visible);
  // Set sheet visibility to hidden
  selectedSheet.setVisibility(ExcelScript.SheetVisibility.hidden);
}

Accepted answer

0 additional answers

Your answer

Tanay Prasad 2,250 Reputation points

2023-06-28T11:46:27.91+00:00

I am not very clear on the issue.

Do you mind explaining in detail?
Phillip Saulnier 20 Reputation points

2023-06-28T18:53:31.7933333+00:00

i have created a test on a sheet called test and created a script button so another person could submit their answer and return to the sheet called information so the next person can do the test. i also created a script button on the information sheet to take the user to the test sheet so they can do the test, both button hide the sheet they are on. the first time i ran it it worked,but after every time i ran the script to goto the test the test sheet would show but any time i tried to enter data on it it would save the data on the information sheet which was hidden
Tanay Prasad 2,250 Reputation points

2023-06-29T06:42:02.73+00:00

Seems like there might be an issue with how you are referencing the sheets in your script.

Try creating a more simplified version of your worksheet that contains only the essential components (such as two sheets, buttons, and simple data entry). Test the script with this simple configuration to narrow down the problem.
Phillip Saulnier 20 Reputation points

2023-06-29T17:43:30.8766667+00:00

this is the first script that I run on sheet named INFORMATION this unhides sheet test clears the answer cells D18:D27 and hides the INFORMATION sheet function main(workbook: ExcelScript.Workbook) { let tEST = workbook.getWorksheet("TEST"); // Set sheet visibility to visible tEST.setVisibility(ExcelScript.SheetVisibility.visible); // Clear ExcelScript.ClearApplyTo.contents from range D18:D27 on tEST tEST.getRange("D18:D27").clear(ExcelScript.ClearApplyTo.contents); let selectedSheet = workbook.getActiveWorksheet(); // Set sheet visibility to hidden selectedSheet.setVisibility(ExcelScript.SheetVisibility.hidden); } This is the 2nd script that I run on the sheet named test This unhides sheet INFORMATION Clears the cell for user name B15 Hides the TEST sheet function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Clear ExcelScript.ClearApplyTo.contents from range B15 on selectedSheet selectedSheet.getRange("B15").clear(ExcelScript.ClearApplyTo.contents); let iNFORMATION = workbook.getWorksheet("INFORMATION"); // Set sheet visibility to visible iNFORMATION.setVisibility(ExcelScript.SheetVisibility.visible); // Set sheet visibility to hidden selectedSheet.setVisibility(ExcelScript.SheetVisibility.hidden); }

Answer 1

Tanay Prasad 2,250

Well, the code seems to be fine only.

I suggest you check this out-

Verify that the "TEST" sheet's data entry code is pointing to the appropriate range for entering the data. Make sure your data entry code on the "TEST" sheet specifically refers to the range D18:D27.
Look for any other code or event handlers that might be interfering: It's likely that the workbook's other event handlers or code, which are activated when data is input, are sending the data entry to the incorrect sheet.

Share via

office script in excel on web

0 additional answers

Your answer