I have data to be read from another sheet (Master Sheet) within a range
Master!A1:EE2000
and to be written on a separate sheet (Sheet A) with the same values (no alterations performed) and location.
This is the code I used for the read and write actions (using Sheets API).
function SetGetData() {
//Send Data to Master Spreadsheet
SpreadsheetApp.getActive().toast("Retrieving data from all teams.", "Please wait... ⏳", 30);
//Get Values from B Sheet
Logger.log("Getting Data from B Sheet.");
var myssID = scriptproperties.getProperty("ssID");
var myss = SpreadsheetApp.openById(myssID);
var sheetno = JSON.parse(scriptproperties.getProperty('TeamSheetNo'));
var sheetname = sheetno.toString().concat("B")
Logger.log("-- Sheet Name: " + sheetname);
var mysheet = myss.getSheetByName(sheetname);
var mydatarange = mysheet.getDataRange();
var lastcol = mydatarange.getLastColumn();
var lastrow = mydatarange.getLastRow();
var mydatarangeNotation = sheetname+"!A1:EE"+lastrow;
Logger.log("Get Data: SS: "+sheetno+" & Range: "+mydatarangeNotation);
//var mydata = mysheet.getRange(1,1,lastrow,lastcol).getValues();
var mydata = Sheets.Spreadsheets.Values.get(myssID, mydatarangeNotation).values;
Logger.log("Getting Data Complete!");
//Sending B Sheet Values to Master Spreadsheet
Logger.log("Sending Data to Master Spreadsheet");
var masterID = "XXXXXXXX";
var ss = SpreadsheetApp.openById(masterID);
var sheet = ss.getSheetByName(sheetname);
Logger.log("-- Clearing existing data on " + sheetname + " Sheet.");
sheet.getDataRange().clearContent();
Logger.log("-- Existing data has been cleared. Proceeding with setting values.")
Logger.log("Set Data: SS: Master & Range: "+mydatarangeNotation);
//sheet.getRange(1,1,lastrow,lastcol).setValues(mydata);
Sheets.Spreadsheets.Values.update({values: mydata}, masterID, mydatarangeNotation, {valueInputOption: "USER_ENTERED"});
Logger.log("Values on Master Spreadsheet has been updated.");
//Get Data from Master Spreadsheet Back to this Spreadsheet
Logger.log("Retriving data from all Teams.");
var masterdatarange = ss.getSheetByName("Master").getDataRange();
var masterlastcol = masterdatarange.getLastColumn();
var masterlastrow = masterdatarange.getLastRow();
var masterrangeNotation = "Master!A1:EE"+masterlastrow;
Logger.log("Get Data: SS: Master & Range: "+masterrangeNotation);
//var masterdata= ss.getSheetByName("Master").getRange(1,1,masterlastrow,masterlastcol).getValues();
var masterdata = Sheets.Spreadsheets.Values.get(masterID, masterrangeNotation).values;
var myssmaster = myss.getSheetByName("Master");
myssmaster.getDataRange().clearContent();
Logger.log("-- Existing data has been cleared. Proceeding with setting values.")
Logger.log("-- Writing data to this spreadsheet Master Sheet.");
Logger.log("Set Data: SS: "+sheetno+" & Range: "+masterrangeNotation);
//myssmaster.getRange(1,1,masterlastrow,masterlastcol).setValues(masterdata);
Sheets.Spreadsheets.Values.update({values: masterdata}, myssID, masterrangeNotation, {valueInputOption: "USER_ENTERED"});
SpreadsheetApp.flush();
SpreadsheetApp.getActive().toast("Workload has now been updated.", "Complete! 🏆", 10);
Logger.log("This spreadsheet Master Sheet has been updated.");
var timestamp = myssmaster.getRange("A1").getValue();
Logger.log("Timestamp: " + timestamp);
}
Everything seems fine if I run it through Editor or by using a button however, if it runs using Time-driven trigger (every 10 mins), Sheets.Spreadsheets.Values.update was executed but no data were written, sometimes it does but sometimes doesn't. I have 2 instances of read and 2 instances of write in a single function for one sheet.
I need to transfer data on Sheet A to the Master Sheet make some processing there and transfer the processed data back to Sheet A. See diagram for visuals. Data Transfer Diagram
I have a total of 20 sheets that perform the same code. I don't know if the Quota was reached because I've got no error in the execution logs.
I tried using getValues and setValues first but it keeps exceeding execution time due to a large data and that's how I came up using Sheets API.
My main objective is to automatically update Sheet A based on the data in the Master Sheet.
I'm posting this in case someone has the same issue and if anyone can explain more in detail how it works. (I'm still a newbie in this programming language and I apologize for my English.)
My code now works as it should whenever the time-driven trigger runs.
I just replaced sheet.getDataRange().clearContent() to a Sheets API Sheets.Spreadsheets.Values.clear({},masterID,mydatarangeNotation)
and myssmaster.getDataRange().clearContent() to Sheets.Spreadsheets.Values.clear({},myssID,"Master!A1:EE5002")
I think for the action involving read, clear and write data on Google Sheets to work smoothly it should have come from the same service whether using the Apps Script's built-in Sheets service Spreadsheetapp or the Sheets API Sheets.Spreadsheets.Values.
Modified Script:
I used Tanaike's 1st modified script (Thanks, Tanaike).
function SetGetData() {
//Send Data to Master Spreadsheet
// SpreadsheetApp.getActive().toast("Retrieving data from all teams.", "Please wait... ⏳", 30); // Removed
//Get Values from B Sheet
Logger.log("Getting Data from B Sheet.");
var myssID = scriptproperties.getProperty("ssID");
var myss = SpreadsheetApp.openById(myssID);
var sheetno = JSON.parse(scriptproperties.getProperty('TeamSheetNo'));
var sheetname = sheetno.toString().concat("B")
Logger.log("-- Sheet Name: " + sheetname);
var mysheet = myss.getSheetByName(sheetname);
var mydatarange = mysheet.getDataRange();
var lastcol = mydatarange.getLastColumn();
var lastrow = mydatarange.getLastRow();
var mydatarangeNotation = sheetname + "!A1:EE" + lastrow;
Logger.log("Get Data: SS: " + sheetno + " & Range: " + mydatarangeNotation);
//var mydata = mysheet.getRange(1,1,lastrow,lastcol).getValues();
var mydata = Sheets.Spreadsheets.Values.get(myssID, mydatarangeNotation).values;
Logger.log("Getting Data Complete!");
//Sending B Sheet Values to Master Spreadsheet
Logger.log("Sending Data to Master Spreadsheet");
var masterID = "XXXXXXXX";
var ss = SpreadsheetApp.openById(masterID);
var sheet = ss.getSheetByName(sheetname);
Logger.log("-- Clearing existing data on " + sheetname + " Sheet.");
Sheets.Spreadsheets.Values.clear({},masterID,mydatarangeNotation); //Modified
//sheet.getDataRange().clearContent();
Logger.log("-- Existing data has been cleared. Proceeding with setting values.")
Logger.log("Set Data: SS: Master & Range: " + mydatarangeNotation);
//sheet.getRange(1,1,lastrow,lastcol).setValues(mydata);
Sheets.Spreadsheets.Values.update({ values: mydata }, masterID, mydatarangeNotation, { valueInputOption: "USER_ENTERED" });
Logger.log("Values on Master Spreadsheet has been updated.");
//Get Data from Master Spreadsheet Back to this Spreadsheet
Logger.log("Retriving data from all Teams.");
var masterdatarange = ss.getSheetByName("Master").getDataRange();
var masterlastcol = masterdatarange.getLastColumn();
var masterlastrow = masterdatarange.getLastRow();
var masterrangeNotation = "Master!A1:EE" + masterlastrow;
Logger.log("Get Data: SS: Master & Range: " + masterrangeNotation);
//var masterdata= ss.getSheetByName("Master").getRange(1,1,masterlastrow,masterlastcol).getValues();
var masterdata = Sheets.Spreadsheets.Values.get(masterID, masterrangeNotation).values;
var myssmaster = myss.getSheetByName("Master");
Sheets.Spreadsheets.Values.clear({},myssID,"Master!A1:EE5002"); //Modified
//myssmaster.getDataRange().clearContent();
Logger.log("-- Existing data has been cleared. Proceeding with setting values.")
Logger.log("-- Writing data to this spreadsheet Master Sheet.");
Logger.log("Set Data: SS: " + sheetno + " & Range: " + masterrangeNotation);
//myssmaster.getRange(1,1,masterlastrow,masterlastcol).setValues(masterdata);
Sheets.Spreadsheets.Values.update({ values: masterdata }, myssID, masterrangeNotation, { valueInputOption: "USER_ENTERED" });
SpreadsheetApp.flush();
// SpreadsheetApp.getActive().toast("Workload has now been updated.", "Complete! 🏆", 10); // Removed
Logger.log("This spreadsheet Master Sheet has been updated.");
var timestamp = myssmaster.getRange("A1").getValue();
Logger.log("Timestamp: " + timestamp);
}