ImportRange Alternative in Google Sheets via Appscript
$30-250 USD
Geschlossen
Veröffentlicht vor etwa 5 Jahren
$30-250 USD
Bezahlt bei Lieferung
Hello,
I have a very heavy Google Spreadsheet file (it has A LOT of formulas), from where I need to import all data on its tab "DISPALL", to a separate Google Spreadsheet file (to tab "DISPALLCopy").
The importRange() formula is not reliable since it sometimes (randomly) outputs "Loading..." or "Import Error...". I have searched several forums online and many people agree that this formula is not reliable to import data from files with lots of formulated data.
The alternative is to copy and paste the data between files using Google Appscripts. I have tried this but my current script takes forever and many times fails due to time-out error.
I need your help creating a solution, that I could trigger every 15 minutes, and that it takes under 30 seconds tu execute.
The source tab is "DISPALL" on the following Google Spreadsheet file:
https://docs.google.com/spreadsheets/d/1wRscFjRxfU9IlUiunT2EUxohceRljGkwUVOqjMc41Xc/edit#gid=0
The output tab is "DISPALLCopy" on file:
https://docs.google.com/spreadsheets/d/1hSFuksdyJDYeQzEY8dw4H_nK_QQpPUW8JBKUErzX5yc/edit#gid=936196741
The script I am using is as follows:
function onOpen() {
var ui = [login to view URL]();
[login to view URL]('Actualizar Pestañas...')
.addItem('Update "DISPALLCopy"', 'updateTabDISPALLCopy')
.addToUi();
}
function updateTabDISPALLCopy() {
//DISPALL
var fromSpreadSheetID = "1wRscFjRxfU9IlUiunT2EUxohceRljGkwUVOqjMc41Xc"; //Source File
var fromSheetName = "DISPALL";
var toSpreadSheetID = [login to view URL]().getId(); //This Spreadsheet File
var toSheetName = "DISPALLCopy";
copyPasteRange(fromSpreadSheetID,fromSheetName,toSpreadSheetID,toSheetName);
var currentDateTime = [login to view URL](new Date(), "GMT-5", "dd/MM/yyyy hh:mm a");
[login to view URL]().getSheetByName("Stock Datestamp Update").getRange("A2").setValue(toSheetName);
[login to view URL]().getSheetByName("Stock Datestamp Update").getRange("B2").setValue(currentDateTime);
}
function copyPasteRange(fromSpreadSheetID,fromSheetName,toSpreadsheetID,toSheetName) {
var sss = [login to view URL](fromSpreadSheetID); // sss = source spreadsheet
var ss = [login to view URL](fromSheetName); // ss = source sheet
//Get full range of data
var SRange = [login to view URL]();
//get A1 notation identifying the range
var A1Range = [login to view URL]();
//get the data values in range
var SData = [login to view URL]();
var tss = [login to view URL](toSpreadsheetID); // tss = target spreadsheet
var ts = [login to view URL](toSheetName); // ts = target sheet
//set the target range to the values of the source data
[login to view URL]().clear(); //clear target sheet before pasting data
[login to view URL](A1Range).setValues(SData);
}
Hello there.
I am highly interested in your project I read your project descriptions carefully before bidding. your 100% satisfaction is assured if you allow me to serve. First please chat with me where we can talk about briefly.
Regards,
Aakash Y
Dear Clint,
I read your project description. I'm understanding your requirement for this job. I've more than 3 years experience in related field.
I've experience in Data entry, Internet Research, PDF conversation, Social Media Marketing, Search Engine Optimization, Adobe Photoshop, Ecommerce Site Product listing and other admin support jobs. Also expert in Microsoft excel . My typing speed is 50WPM.
Let’s go for interview to justify myself and I have high speed internet connection and availability more than 12-14 hours/day through Skype. I will be highly glad to you.
Best regards,
Ekramool.