after previous post Script setValue based on the values of column based on value of other column matching reference
where i collected sheet IDs, currently almost 300
im trying to edit ALL those spreadsheets from theirs IDs
function update()
{
const ss = SpreadsheetApp.getActiveSpreadsheet(); // Source spreadsheet
const src = ss.getSheetByName('Relação'); // Source sheet
const LR = src.getLastRow();
const Target_Id = src.getRange("B2:B" LR).getValues(); // Target IDs
for (var i = 0; i < LR; i )
{
if ( Target_Id[i] !== "" ) // check to skip "" rows
{
var ss_t = SpreadsheetApp.openById(Target_Id[i]); // Target spreadsheet
var trg = ss_t.getSheetByName('Config'); // Target sheet
trg.getRange("B24").setValue("TEST");
}
}
}
and it works untill one row in column B is "" blank
Exception: Invalid argument: id
update @ script.gs:14
aparently my check
if ( Target_Id[i] !== "" )
isnt working the way i tried, dont really understand why, thats my main question
question 2: as i tested, i know since im trying to update almost 300 sheets, im gonna run into another problem
Exceeded maximum execution time
to update everyting is going to be slow, hit that error around ID 40, so is there a way to optimize the process so i can be able to update all spreadsheets? for now im tying to just update 1 cell, as proof of concept, end goal is entire sheets with formulas, formating, etc but thats a topic for future post if needed
CodePudding user response:
In your script, please modify your script as follows.
From:
Target_Id[i] !== ""
To:
Target_Id[i][0] != ""
- The value retrieved by
getValues()
is 2 dimensional array. By this, I proposed the above modification.