Jump from one spreadsheet to another using Google App Script

user_16342780 0
Viewed 535

I have a script which only works when I am in the current sheet where the code is being run. In A1, I have a checkbox whose job is to refresh api requests. So my code below clicks on the checkbox and fetches new data. So the purpose of my code is basically to click on the checkbox in A1. However, running this code only activates the checkbox but doesn't fetch the new results if I am not in the target spreadsheet. However, the moment I enter the target spreadsheet, the checkbox becomes actually "active" and not just checked. Important to mention that I use SyncWith Addon to fetch requests and it's the one placing the checkboxes in A1 for fetching new results. So my code is just to click on the checkbox inside A1.

If I am on a different sheet and run the code, it doesn't work. I have tried to use setActiveSheet() but it doesn't work. If my code can switch/jump spreadsheet tabs then that's sorted.

Screenshot of what I mean by jumping from Spreadsheet to current enter image description here

TO:

enter image description here

function clear_paste_run() {
var s = SpreadsheetApp.openById("thespreadsheetID");
var sheet = s.getSheetByName("Test");
sheet.getRange("A1").clearContent();
var cell = sheet.getRange("A1");
cell.setFormula('=TEXT(NOW(),"HH:MM")'); 

var ssss = SpreadsheetApp.openById("thespreadsheetID");
var sheetSs = ssss.getSheetByName("Test");
var AG1val = sheetSs.getRange('B1').getValue(); // get the value of B1
sheetSs.getRange("B2").setValue(AG1val); // set the value of B1 to B2

  var ss = SpreadsheetApp.openById("thespreadsheetID");
  var ssheet = ss.getSheetByName("Test");
  var testrange = ssheet.getRange('B2');
  var testvalue = (testrange.getValues());
 
 //Run Nov
  if ( testvalue == '120') {

var spreadsheet = SpreadsheetApp.openById("thespreadsheetID");

SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[5]);
spreadsheet.getRange("A1").check();
 }
google-apps-script google-sheets
1 Answers

I believe your goal is as follows.

  • You want to move from the current sheet to the specification sheet in the same Google Spreadsheet.
  • And, you want to run this script every 1 minute.

When you want to move from the current sheet to the specification sheet in the same Google Spreadsheet, your script is required to modify as follows.

From:

SpreadsheetApp.openById("thespreadsheetID")

To:

SpreadsheetApp.getActiveSpreadsheet()

But when you want to run this script every 1 minute, unfortunately, this cannot be achieved using the time-driven trigger. So in this case, it is required to use a workaround. When this workaround is used, the sample script is as follows.

Sample script:

This sample script uses the sidebar.

Google Apps Script side: Code.gs

Please copy and paste the following script to the script editor of Spreadsheet as a script.

// Please run this function.
function openSidebar() {
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("index"));
}

function clear_paste_run() {

    // do something
    // When you want to use your script, please modify `SpreadsheetApp.openById("thespreadsheetID")` to `SpreadsheetApp.getActiveSpreadsheet()`.

}

HTML and Javascript side: index.html

Please copy and paste the following script to the script editor of the Spreadsheet as HTML.

<input type="button" onclick="start()" value="start">
<input type="button" onclick="stop()" value="stop">
<script>

let run;
const runGoogleScript = _ => new Promise((resolve, reject) => google.script.run.withFailureHandler(reject).withSuccessHandler(resolve).clear_paste_run());
const main = _ => new Promise((resolve, reject) => setTimeout(async () => await runGoogleScript().then(resolve).catch(reject), 60000));

async function start() {
  run = true;
  console.log("Start.")
  while (run) await main();
  console.log("End.")
}

function stop() {
  run = false;
}
</script>

Testing.

When you run openSidebar, a sidebar is opened on Google Spreadsheet. And, when you click "start" button, the script is run. In this case, the function of clear_paste_run is run every 1 minute. When you use your script by modifying from SpreadsheetApp.openById("thespreadsheetID") to SpreadsheetApp.getActiveSpreadsheet(), the sheet is moved.

Reference:

user_71086530
Related