How to automate moving data from one google sheet to another

0
votes

I am trying to send values from Google sheets to Firebase so that the data table updates automatically. To do this I used Google Drive CMS which exported the data to Firebase perfectly. The problem is that I scrape data off of websites. For example, I get a list of data through the use of importXML:

=IMPORTXML("https://www.congress.gov/search?q=%7B%22source%22%3A%22legislation%22%7D", "//li[@class='expanded']/span[@class='result-heading']/a[1]")

CMS doesn't seem to get the values that this formula results in but the actual formula which causes errors. The way I adressed this is to make a new tab that has the formulas and keep the CMS tab with the value only. I've been copying and pasting this manually but want to make that process automatic. I cannot find any help to make a script that takes the values of the formulas from one tab and place those values in a different sheet.

Here are some pictures for reference:

Data with formulaData without formulaData sent to Firebase

*I put the blue highlight on the cell I am referring to for the google sheets and the data from the firebase is showing the first row of data that was exported.

1
firebasegoogle-apps-scriptfirebase-realtime-databasegoogle-sheetscontent-management-system

1 Answers

1
votes

How about this sample script? Please think of this as one of several answers. The flow of this script is as follows. When you use this script, please copy and paste it and run sample().

Flow :

  1. Input a source range of active sheet.
  2. Retrieve the source range.
  3. Input a destination range of a destination spreadsheet.
  4. Retrieve the destination range of the destination sheet.
  5. Copy data from the source range to the destination range.
    • Data which included values, formulas and formats is copied.

Sample script :

function sample() {
  // Source
  var range = "a1:b5"; // Source range
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcrange = ss.getActiveSheet().getRange(range);

  // Destination
  var range = "c1:d5"; // Destination range,
  var dstid = "### file id ###"; // Destination spreadsheet ID
  var dst = "### sheet name ###"; // Destination sheet name
  var dstrange = SpreadsheetApp.openById(dstid).getSheetByName(dst).getRange(range);

  var dstSS = dstrange.getSheet().getParent();
  var copiedsheet = srcrange.getSheet().copyTo(dstSS);
  copiedsheet.getRange(srcrange.getA1Notation()).copyTo(dstrange);
  dstSS.deleteSheet(copiedsheet);
}

If I misunderstand your question, I'm sorry.

Edit :

This sample script copies from values of source spreadsheet to destination spreadsheet.

function sample() {
  // Source
  var range = "a1:b5"; // Source range
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcrange = ss.getActiveSheet().getRange(range);

  // Destination
  var range = "c1:d5"; // Destination range,
  var dstid = "### file id ###"; // Destination spreadsheet ID
  var dst = "### sheet name ###"; // Destination sheet name
  var dstrange = SpreadsheetApp.openById(dstid).getSheetByName(dst).getRange(range);

  var dstSS = dstrange.getSheet().getParent();
  var sourceValues = srcrange.getValues();
  dstrange.setValues(sourceValues);
}