I cannot get my office script to write any data in the “Data” sheet. I get no errors from running the script and it does say it writes the data but then nothing is there… I’ve tried everything I know to do at this point so I am thankful for any direction that be can be given!
Here is my script:
> > /**
> This script submits data from the "Setup" sheet to the "Data" sheet,
> adds the data to the next available line (skipping a line between entries),
> and clears only the contents of the input fields on the "Setup" sheet, retaining validation.
> */
> function main(workbook: ExcelScript.Workbook) {
> Access the "Setup" and "Data" sheets
> const setupSheet = workbook.getWorksheet("Setup");
> let dataSheet = workbook.getWorksheet("Data");
>
> Create "Data" sheet if it doesn't exist
> if (!dataSheet) {
> dataSheet = workbook.addWorksheet("Data");
> Optionally, set up headers in the "Data" sheet
> dataSheet.getRange("A1:H1").values = [["G4", "G6", "G8", "D10", "D11", "D12", "D15", "D23"]];
> > }
>
> Get data from specified cells on the "Setup" sheet
> const values = [
> setupSheet.getRange("G4").getText(),
> setupSheet.getRange("G6").getText(),
> setupSheet.getRange("G8").getText(),
> setupSheet.getRange("D10").getText(),
> setupSheet.getRange("D11").getText(),
> setupSheet.getRange("D12").getText(),
> setupSheet.getRange("D15").getText(),
> setupSheet.getRange("D23").getText(),
> > ];
>
> Find the next available row on the "Data" sheet, skipping a row
> const usedRange = dataSheet.getUsedRange();
> let lastRow = usedRange ? usedRange.getRowCount() : 1; // If no data, start at row 1
> let nextRow = lastRow + 2; // Skip a row after the last data row
>
> Add data to the next row (skipping a row in between entries)
> const targetRange = dataSheet.getRange(`A${nextRow}:H${nextRow}`);
> targetRange.values = [values];
>
> Clear only the contents of the input cells on the "Setup" sheet
> setupSheet.getRange("G4").clear(ExcelScript.ClearApplyTo.contents);
> setupSheet.getRange("G6").clear(ExcelScript.ClearApplyTo.contents);
> setupSheet.getRange("G8").clear(ExcelScript.ClearApplyTo.contents);
> setupSheet.getRange("D10").clear(ExcelScript.ClearApplyTo.contents);
> setupSheet.getRange("D11").clear(ExcelScript.ClearApplyTo.contents);
> setupSheet.getRange("D12").clear(ExcelScript.ClearApplyTo.contents);
> setupSheet.getRange("D15").clear(ExcelScript.ClearApplyTo.contents);
> setupSheet.getRange("D23").clear(ExcelScript.ClearApplyTo.contents);
> > }
>
>
I’ve tried several variations of this script, even using a simple script that would just enter “Text” into A1 and nothing is working. Workbook is fresh on my PC, no protections set to workbook. I appreciate any help!