Dataclip backup

refresh.js

function refreshDataClip() {
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var urlCell = sheet.getRange('A1');
  
  var cellFormula = urlCell.getFormula();
  
  var documentProperties = PropertiesService.getDocumentProperties();
  
  var originalUrl = getStringFromFormula(cellFormula);
  
  if (originalUrl != undefined) {
    documentProperties.setProperty('DATACLIP_URL_' + sheet.getName(), originalUrl);
    url = originalUrl;
  } else {
    url = documentProperties.getProperty('DATACLIP_URL_' + sheet.getName()); 
  }
  
  
  var text = UrlFetchApp.fetch(url).getContentText();

  var csv = Utilities.parseCsv(text);
    
  sheet.getRange(1,1,csv.length, csv[0].length).setValues(csv);
  
}

function getStringFromFormula(formula) {
  var prefix = '\\"';
  var suffix = '\\"';
  var prefixToSearchFor = new RegExp(prefix, "i");
  var suffixToSearchFor = new RegExp(suffix, "i");
  var prefixLength = 1;
  
  if (formula[0] == "=") {
        
    // find the prefix
    prefixFoundAt = formula.search(prefixToSearchFor);
    if (prefixFoundAt >= 0) { // yes, this cell contains the prefix
      // remove everything up to and including the prefix
      extractedTextString = formula.slice(prefixFoundAt + prefixLength);
      // find the suffix
      suffixFoundAt = extractedTextString.search(suffixToSearchFor);
      if (suffixFoundAt >= 0) { // yes, this cell contains the suffix
        // remove all text from and including the suffix
        extractedTextString = extractedTextString.slice(0, suffixFoundAt).trim();
        
        // store the plain hyperlink string in the cell, replacing the formula
        
        Logger.log(extractedTextString);
        return extractedTextString;
      }
    }
  }
  
}


//add a menu when the spreadsheet is opened
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet.addMenu("Dataclip", [{name: "Refresh", functionName: "refreshDataClip"}]);  
}