CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Andrew Ryan
Andrew Ryan

Reputation: 13

CopyPasteType.PASTE_VALUES does not copy only values in Google Sheets

I am working on a simple project that imports COVID data to a Google Sheet on a daily basis. I am running into a problem that since I am using IMPORTHTML the tables are updating every time the table updates on the website I am using. I don't want this, since the goal is to keep tables from each day's stats. My solution was to copy the table that I import and paste it (values only) right on top of itself. Everything is working, including copy and paste, but PASTE_VALUES does not seem to be working. When I do it manually it solves my problem, but not when done with code. Here is my code.

function getData() { 


  //get yesterdays date for sheet name and title of table
  const today = new Date()
  const yesterday = new Date(today)

  yesterday.setDate(yesterday.getDate() - 1)

  today.toDateString()
  yesterday.toDateString()


  //open up a new spreadsheet with yesterday's date as name
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var yourNewSheet = activeSpreadsheet.getSheetByName(yesterday);

  if (yourNewSheet != null) {
    activeSpreadsheet.deleteSheet(yesterday);
  }

  yourNewSheet = activeSpreadsheet.insertSheet();
  yourNewSheet.setName(yesterday);
  yourNewSheet.activate();  


  //import table into new sheet and put yesterday's date as title on top of table
  //var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(yesterday); 
  var queryString = Math.random(); 
  var cellFunction = '= IMPORTHTML("https://www.worldometers.info/coronavirus/country/us/","table", 2)'

  yourNewSheet.getRange('E1').setValue(yesterday);
  yourNewSheet.getRange('A3').setValue(cellFunction); 


  // copy and paste the range to values only so that the importHTML function doesn't download new data when it updates every day
  yourNewSheet.getRange('A3:Z100').copyTo(yourNewSheet.getRange('A3:Z100'), SpreadsheetApp.CopyPasteType.PASTE_VALUES);


}```

Upvotes: 1

Views: 5544

Answers (1)

zummon
zummon

Reputation: 986

I met this situation before, it's because copyTo(...) has 2 ways to declare

1) .copyTo(destination, copyPasteType, transposed) 

yourNewSheet.getRange('A3:Z100').copyTo(yourNewSheet.getRange('A3:Z100'), 
SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)

put more , false if not it will read as 2) which is it kinda mean nothing I think

2) .copyTo(destination, options) 

yourNewSheet.getRange('A3:Z100').copyTo(yourNewSheet.getRange('A3:Z100'), 
{contentsOnly:true})

for this options need to put {contentsOnly:true} instead

you can use one of these above, the result will be the same

Note:

Also, before copying the range, make sure the script updates by using SpreadsheetApp.flush(). Otherwise the values generated by the formula might end up not getting copied at all.

Upvotes: 3

Related Questions