0

I set up a simple script to download a CSV into a Google Sheet. I saved the code, gave permissions on running, ran it from the script editor associated with the correct sheet, double and triple checked that the sheet name is consistent between script and sheet...and still I get an error saying that the sheet cannot be found when I run the script, even though I am in that account and looking at it. It's driving me nuts. Has anyone encountered this, or do you have any suggestions?

// URL of the CSV file containing hourly data
const CSV_URL = "https://mysource.com/data.csv";

// Name of your Google Sheet where the data will be imported
const SHEET_NAME = "datafeed";

function fetchDataAndUpdateSheet() {
  try {
    // Fetch data from the CSV URL
    const response = UrlFetchApp.fetch(CSV_URL);
    const csvData = Utilities.parseCsv(response.getContentText());
    
    // Get the sheet object
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
    
    // Check if sheet exists and handle potential error
    if (!sheet) {
      throw new Error("Sheet not found: " + SHEET_NAME);
    }
    
    // Clear existing data (optional, adjust as needed)
    sheet.clearContents();
    
    // Write fetched data to the sheet
    sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
    
    Logger.log("Data fetched from '" + CSV_URL + "' and sheet updated successfully!");
  } catch (error) {
    Logger.log("Error: " + error.message);
    // Optional: Send email notification on error (requires setting up a trigger)
    // ... (code to send email notification)
  }
}

// Set up a trigger to run the function hourly
function createTrigger() {
  ScriptApp.newTrigger('fetchDataAndUpdateSheet')
      .timeBased()
      .everyHours(1)
      .create();
}

// Run the trigger creation function on script load
function onOpen() {
  createTrigger();
}
Execution log
2:51:02 PM  Notice  Execution started
2:51:05 PM  Info    Error: Sheet not found: datafeed
2:51:04 PM  Notice  Execution completed
2
  • 2
    Set the breakpoint to the line "if (!sheet) {". Then run debugging and look at the breakpoint, what is in your sheet variable? Commented Mar 21 at 22:01
  • Don't use a custom error handler during development. It hides actual errors and are hard to debug. Just let it fail, so that you can fix it. Rename your sheet to something simpler like s1.
    – TheMaster
    Commented Mar 23 at 2:29

1 Answer 1

2

Chances are that the sheet name really differs from datafeed. Look for things like leading or trailing spaces.

One way to make sure that the sheet name matches is to copy and paste the sheet name from the tab bar to the code, or vice versa.

The test can be rewritten like this to let you better detect the issue:

    if (!sheet) {
      throw new Error(`Cannot find sheet '${SHEET_NAME}'. getSheetByName() returned '${String(sheet)}'.`);
    }
2
  • Yes thank you for your reply. I have checked many times and also done as you suggest already. That is definitely not the issue.
    – Drewbot
    Commented Mar 21 at 16:01
  • @Drewbot as doubleunary suggested your code is fine so try testing with a different sheet name. It will be difficult for us to solve for you. Don't skip the obvious. Created a new test spreadsheet and script, etc.
    – Blindspots
    Commented Mar 22 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.