I have a small script and what I'm trying to do is to write one value from 'Sheet 1' to 'Sheet 2'. Wait for the results to load and pare the cells to see if it is above 10% or not. I have some =importhtml functions in the spreadsheet and it takes along time to load. I've tried sleep, utilities sleep, and flush. None have been working, maybe because I might be putting it in the wrong area..
function pareCells() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var listSheet = ss.getSheetByName('Stocks');
var dataSheet = ss.getSheetByName('Summary');
var listSheetLastRow = listSheet.getLastRow();
var currRow = 1;
for (i = 1; i <= listSheetLastRow; i++) {
if (listSheet.getRange(1, 3).getValue() == 'asd') {
var ticker = listSheet.getRange(currRow, 1).getValue();
dataSheet.getRange(5, 4).setValue(ticker);
var value1 = dataSheet.getRange(15, 4).getValue();
var value2 = dataSheet.getRange(22, 4).getValue();
SpreadsheetApp.flush();
if (value1 > 0.10 && value2 > 0.10) {
listSheet.getRange(currRow, 8).setValue('True');
listSheet.getRange(currRow, 9).setValue(value1);
listSheet.getRange(currRow, 10).setValue(value2);
} else {
listSheet.getRange(currRow, 8).setValue('False');
}
} else {
Browser.msgBox('Script aborted');
return null;
}
currRow++;
}
}
I have a small script and what I'm trying to do is to write one value from 'Sheet 1' to 'Sheet 2'. Wait for the results to load and pare the cells to see if it is above 10% or not. I have some =importhtml functions in the spreadsheet and it takes along time to load. I've tried sleep, utilities sleep, and flush. None have been working, maybe because I might be putting it in the wrong area..
function pareCells() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var listSheet = ss.getSheetByName('Stocks');
var dataSheet = ss.getSheetByName('Summary');
var listSheetLastRow = listSheet.getLastRow();
var currRow = 1;
for (i = 1; i <= listSheetLastRow; i++) {
if (listSheet.getRange(1, 3).getValue() == 'asd') {
var ticker = listSheet.getRange(currRow, 1).getValue();
dataSheet.getRange(5, 4).setValue(ticker);
var value1 = dataSheet.getRange(15, 4).getValue();
var value2 = dataSheet.getRange(22, 4).getValue();
SpreadsheetApp.flush();
if (value1 > 0.10 && value2 > 0.10) {
listSheet.getRange(currRow, 8).setValue('True');
listSheet.getRange(currRow, 9).setValue(value1);
listSheet.getRange(currRow, 10).setValue(value2);
} else {
listSheet.getRange(currRow, 8).setValue('False');
}
} else {
Browser.msgBox('Script aborted');
return null;
}
currRow++;
}
}
-
Please post more details of what range of values are being imported using
=importhtmlfunction and also if you can add the spreadsheet with the data to analyse. – KRR Commented Dec 18, 2014 at 23:29 - I'm importing financial statements of panies. I've created a valuation method for myself. What I'd like is for that valuation to be done on the entire market. So essentially what I'm doing is, I'm going to have a sheet with a list of stock ticker symbols, I wanted the script to take each symbol input into appropriate field and then wait for the financial statements to be imported, the calculations to be done (roughly takes 10 seconds) and then check if margin of safety meets 10%. If it does then it writes true, and moves to the next one else writes false. Let me know if you want to see it. – user3527050 Commented Dec 19, 2014 at 2:20
3 Answers
Reset to default 4If it is not important that you use the =IMPORTHTML() function in your sheet, the easiest way to do this will be to use UrlFetchApp within Apps Script. Getting the data this way will cause your script to block until the HTML response is returned. You can also create a time-based trigger so your data is always fresh, and the user will not have to wait for the URL fetch when looking at your sheet.
Once you get the HTML response, you can do all of the same processing you'd do in Sheet1 within your script. If that won't work because you have plex processing in Sheet1, you can:
- use
UrlFetchpApp.fetch('http://sample./data.html')to retrieve your data - write the data to Sheet1
- call
SpreadsheetApp.flush()to force the write and whatever subsequent processing - proceed as per your example above
By handling these steps sequentially in your script you guarantee that your later steps don't happen before the data is present.
I had a similar problem but came up with a solution which uses a while loop which forces the script to wait until at least 1 extra column or 1 extra row has been added. So for this to work the formula needs to add data to at least one extra cell other than the one containing the formula, and it needs to extend the sheet's data range (number of rows or columns), for example by adding the formula to the end of the sheet, which looks like what you are doing. Every 0.5 seconds for 10 seconds it checks if extra cells have been added.
dataSheet.getRange(5, 4).setValue(ticker);
var wait = 0;
var timebetween = 500;
var timeout = 10000;
var lastRow = dataSheet.getLastRow();
var lastColumn = dataSheet.getLastColumn();
while (dataSheet.getLastColumn() <= lastColumn && dataSheet.getLastRow() <= lastRow){
Utilities.sleep(timebetween);
wait += timebetween;
if (wait >= timeout){
Logger.log('ERROR: Source data for ' + ticker + ' still empty after ' + timeout.toString() + ' seconds.');
throw new Error('Source data for ' + ticker + ' still empty after ' + timeout.toString() + ' seconds.');
}
}
In case if you are getting these two values (
var value1 = dataSheet.getRange(15, 4).getValue();
var value2 = dataSheet.getRange(22, 4).getValue();
) after the =importhtml call, you have to add sleep function before these two lines of code.
You also can have a loop until you get some values into the range from =importhtml call and add some sleep in the loop. Also note that as of April 2014 the limitation of script runtime is 6 minutes.
I also found this link which might be helpful.
Hope that helps!