google-sheetsgoogle-apps-scripttriggers

Can a google-apps-script be written to download a csv file from spreadsheet directly to a local hard drive with an onEdit trigger?


Can a google-apps-script be written to download a csv file from spreadsheet directly to a local hard drive with an onEdit trigger? I don't want it to go to Google drive at all, just when the trigger fires, it will automatically download the .csv file to my local C: drive. Please note that this is not an exact duplicate of the stackoverflow question "How to export to CSV from spreadsheet to drive or download folder" because I want the download to my local C: drive to be unprompted/automatic and not pop up something that needs to have human interaction in order to complete. I might be mistaken, but I don't think that happens with that question. It seems to me also that that question actually only - or at least mainly - refers to saving to my Google drive instead of my local C: drive


Solution

  • Please review the comments on the original question that help clarify exactly what I was after with this post. (site A and site B)

    First of all, thanks to TheMaster for helping me with the coding basis of what I'll provide below.

    What I found with my testing is that basically, the onEdit trigger seems to only work on the instance of the spreadsheet that is actually performing the edits. It does not trigger on any of the other instances of the spreadsheet that might be open simultaneously. So my initial thought on this being possible, is actually erroneous. Unless someone can comment otherwise, I am going to answer my own question and say that it is not possible - or at least not so easily.

    At any rate, below is the modification of TheMaster's code for .pdf downloads that makes the download be a .csv instead for those that might want it.

    To summarize, the modification shown below worked to save a .csv to the local drive on the spreadsheet instance that was performing the edit. It did not, however save a .csv to the local drive on the other instance of the spreadsheet that I had open in a different location.

    function downloadCsvToDesktop() {
      var ss = SpreadsheetApp.getActive(),
        id = ss.getId(),
        sht = ss.getActiveSheet(),
        shtId = sht.getSheetId(),
        url =
          'https://docs.google.com/spreadsheets/d/' +
          id +
          '/export' +
          '?format=csv&gid=' +
          shtId;
      var val = 'CSVNAME';//custom .csv name here 
      val += '.csv';
      //can't download with a different filename directly from server
      //download and remove content-disposition header and serve as a dataURI
      //Use anchor tag's download attribute to provide a custom filename
      var res = UrlFetchApp.fetch(url, {
        headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
      });
      SpreadsheetApp.getUi().showModelessDialog(
        HtmlService.createHtmlOutput(
          '<a target ="_blank" download="' +
            val +
            '" href = "data:application/pdf;base64,' +
            Utilities.base64Encode(res.getContent()) +
            '">Click here</a> to download, if download did not start automatically' +
            '<script> \
            var a = document.querySelector("a"); \
            a.addEventListener("click",()=>{setTimeout(google.script.host.close,10)}); \
            a.click(); \
            </script>'
        ).setHeight(50),
        'Downloading CSV..'
      );
    }