Script to cause an ImportRange function to run on demand

0
votes

first, let me say, i am not a programmer, but i understand and can build spreadsheet ok. I'm newer to doing so in Google Sheets. I am using an Importrange function to copy date from one spreadsheet tab to another spreadsheet. I'm finding that there is a delay in the updating. I checked my Settings for recalculations and Google Sheets is set to refresh when data is changed. However, its not doing so. So, i wanted to create a button the user can hit and force the ImportRange refresh.

I found some code that appears to accomplish this, but I can't get it to work:

function myFunction() {
  
  SpreadsheetApp.getActive().getRange('A1').setValue('IMPORTRANGE('https://docs.google.com/spreadsheets/d/abcd123abcd123', 'sheet1!A:B')')
}

Here is my import range which is in CELL C4 of a sheet named CALCS:

=importrange("https://docs.google.com/spreadsheets/d/1B5PEI5f4-TAhS77-poCYTiWI6w2t2BEzfiP5kXVq9lk/edit#gid=1661941505","PO TRACKING!A4:T1000")
2
google-apps-script

2 Answers

0
votes

Welcome to the wonderful world of scripts! There is so much you can do with your projects when using Google Apps Script.

Your question is a good one but I took a different approach. Rather than using an import range that needs to be recalculated, I just wrote a script that would do a similar thing. If you need to collect a specific range of data instead of the entire sheet, then just make one small change to the script provided.

//CHANGE 
var poData = poTab.getDataRange().getValues();
//TO
var poData = poTab.getRange('INSERT A1 NOTATION HERE').getValues();

That should allow you to select a specifc range if needed.

    //TRANSFER DATA FROM ONE TAB TO ANOTHER
function transferFromSameSpreadsheet() {
  
  //GETS ACTIVE SPREADSHEET
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //GETS SPECIFIC SHEET BY NAME WHERE DATA IS STORED
  var poTab = ss.getSheetByName('Po Tracking');
  //STORES ALL DATA FROM SHEET
  var poData = poTab.getDataRange().getValues();
  //GETS SPECIFIC SHEET BY NAME WHERE DATA IS SENT
  var transferTo = ss.getSheetByName('insert sheet name here');
  //CLEARS OLD DATA
  transferTo.clearContents();
  //UPDATES SHEET WITH NEW DATA
  transferTo.getRange(1,1,poData.length,poData[0].length).setValues(poData);
  
  
}


//TRANSFER DATA FROM A MASTER SHEET TO ANOTHER WORKBOOK. RUN SCRIPT IN ACTIVE WORKBOOK, NOT MASTER.
function transferFromDifferentSpreadsheet() {
  
  //OPENS SPREADSHEET BY ID
  var ms = SpreadsheetApp.openById('insert spreadsheet id here');
  //GETS ACTIVE SPREADSHEET
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //GETS SPECIFIC SHEET BY NAME WHERE DATA IS STORED
  var poTab = ms.getSheetByName('Po Tracking');
  //STORES ALL DATA FROM SHEET
  var poData = poTab.getDataRange().getValues();
  //GETS SPECIFIC SHEET BY NAME WHERE DATA IS SENT
  var transferTo = ss.getSheetByName('insert sheet name here');
  //CLEARS OLD DATA
  transferTo.clearContents();
  //UPDATES SHEET WITH NEW DATA
  transferTo.getRange(1,1,poData.length,poData[0].length).setValues(poData);
  
}

Good luck!

Get range A1 notation documentation: https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangea1notation

0
votes

What your code is doing is just setting the cell value as the string you input in the setValue()

If you want to input formula you can either include "=" when using setValue() or use setFormula() method

Sample:

function myFunction() {
  SpreadsheetApp.getActive().getRange('A1').setFormula('IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxx", "sheet1!A:B")');
  
  SpreadsheetApp.getActive().getRange('D1').setValue('=IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxx", "sheet1!A:B")');
}

Note:

I also fixed the string format in your code so that it will provide a string input to the setValue() and setFormula()

References:

Range.setValue()

Range.setFormula()