OfficeScript says it is writing to designated excel sheet, but no text appears and no errors given

Question

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!

taller · Accepted Answer · 2024-11-24 17:30:17Z

setValues is used to populate cells on worksheet.

Microsoft documentation:

ExcelScript.Range interface

/**
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");
    const targetCells = "G4,G6,G8,D10,D11,D12,D15,D23"; 
    // Create "Data" sheet if it doesn't exist
    if (dataSheet) {
        // Optionally, set up headers in the "Data" sheet
        dataSheet.getRange("A1:H1").setValues([targetCells.split(",")]);
    }

    // Get data from specified cells on the "Setup" sheet
    const values: string[] = targetCells.split(",").map(c => setupSheet.getRange(c).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.setValues([values]);

    // Clear only the contents of the input cells on the "Setup" sheet
    setupSheet.getRanges(targetCells).clear(ExcelScript.ClearApplyTo.contents);

}

Collectives™ on Stack Overflow

OfficeScript says it is writing to designated excel sheet, but no text appears and no errors given

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related