Prevent IMPORTDATA from refreshing

user_17101497 0
Viewed 72

I have a script runing which uses IMPORTDATA to capture data from a website. Every hour it goes to the website, and captures a price value, and pasts it into a cell for that hour. So each dat I ended up with 24 cells on a row, populated.

The problem I have is that every hour, EVERY cell is being updated.

So imagine it runs at 11pm and the result is 5,500. What happens is that every single cell that has previously populated changes to 5,500.

From what I understand, it seems like IMPORTDATA is syncing with the websit every hour. This isnt what I want....I would like it so that once the data is imported using importdata, the link is broken and the data never changes.

Does anyone know how I can alter my code to achieve this?

Thanks guys.

function pricesearchsheet2() {

    var date = new Date();
    var currenthour = date.getHours();
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  var targetcell = 1

// set the target cell based on the current hour of the day
  if (currenthour == 0) {
  var targetcell = "B6";
}
if (currenthour == 1) {
  var targetcell = "C6";
}
if (currenthour == 2) {
  var targetcell = "D6";
}
if (currenthour == 3) {
  var targetcell = "E6";
}
if (currenthour == 4) {
  var targetcell = "F6";
}
if (currenthour == 5) {
  var targetcell = "G6";
}
if (currenthour == 6) {
  var targetcell = "H6";
}
if (currenthour == 7) {
  var targetcell = "I6";
}
if (currenthour == 8) {
  var targetcell = "J6";
}
if (currenthour == 9) {
  var targetcell = "K6";
}
if (currenthour == 10) {
  var targetcell = "L6";
}
if (currenthour == 11) {
  var targetcell = "M6";
}
if (currenthour == 12) {
  var targetcell = "N6";
}
if (currenthour == 13) {
  var targetcell = "O6";
}
if (currenthour == 14) {
  var targetcell = "P6";
}
if (currenthour == 15) {
  var targetcell = "Q6";
}
if (currenthour == 16) {
  var targetcell = "R6";
}
if (currenthour == 17) {
  var targetcell = "S6";
}
if (currenthour == 18) {
  var targetcell = "T6";
}
if (currenthour == 19) {
  var targetcell = "U6";
}
if (currenthour == 20) {
  var targetcell = "V6";
}
if (currenthour == 21) {
  var targetcell = "W6";
}
if (currenthour == 22) {
  var targetcell = "X6";
}
if (currenthour == 23) {
  var targetcell = "Y6";
}
if (currenthour == 24) {
  var targetcell = "Z6";
}


    for (var sheet of sheets) {
        sheet.getRange(targetcell)
            .setValue('=--SPLIT(REGEXEXTRACT(JOIN(",",IMPORTDATA(B1)),"LCPrice"":""(.*)"), CHAR(34))')
    }
}
google-apps-script google-sheets
2 Answers

I believe your goal is as follows.

  • You want to put the values on to Spreadsheet with a formula.
  • After the values are put into the Spreadsheet, you want to convert the formula to the values to avoid the values are updated by the refreshed formula.

In this case, how about copying the values using copyTo as the values without using the formula? When your script is run, it becomes as follows.

Modified script:

As one more modification point, I thought that when I saw your if statements, this might be able to be converted to an array. Also, I reflected on this.

function pricesearchsheet2() {
  var date = new Date();
  var currenthour = date.getHours();
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var ar = ["B6", "C6", "D6", "E6", "F6", "G6", "H6", "I6", "J6", "K6", "L6", "M6", "N6", "O6", "P6", "Q6", "R6", "S6", "T6", "U6", "V6", "W6", "X6", "Y6", "Z6"];
  var targetcell = ar[currenthour];
  for (var sheet of sheets) {
    sheet.getRange(targetcell).setValue('=--SPLIT(REGEXEXTRACT(JOIN(",",IMPORTDATA(B1)),"LCPrice"":""(.*)"), CHAR(34))');
    SpreadsheetApp.flush();
    var range = sheet.getDataRange();
    range.copyTo(range, { contentsOnly: true });
  }
}
  • When you run this script, after the formula was put to a cell, the values are copied to the same range as the values without using the formula. By this, the values are fixed.

References:

user_71086530

Try using Coupler.io. It allows copying data from one spreadsheet to another (or from one sheet to another). In your case you need to:

  • On 'Current value' sheet set script to import needed value to cell A1
  • Set up Coupler.io GSheet -> Gsheet integration in Append mode from 'Current value' to 'History' sheet.
  • Set an hourly schedule You will get each hour value copied new value below previous ones to the 'History' sheets.
user_170782070
Related