IMAGE関数を使って画像を出力する際の問題に関して。

Question

実現したいこと

GASで画像出力する際に、隣のセルのURLを基準に画像を表示したい。
しかし、セルの行番号が2〜１１までの表示が繰り返されてしまい、
１２〜以降のセルの行番号の出力ができないので、解決方法を教えてください。

該当のソースコード

function searchSystem () {
	var book = SpreadsheetApp.getActiveSpreadsheet()
	var sheetData = book.getSheetByName('〇〇検索')
	var currentRowIndex = 2

	var headers = [['商品画像URL', '商品画像']]
	sheetData.getRange(1, 1, 1, headers[0].length).setValues(headers)

	// シートに挿入する値
	var values = []

	// 検索
	for (var sc = 1; sc <= 100000; sc++) {
        var rootHtml = getHtml('サイトのURL' + sc)
          
		if (!rootHtml || rootHtml.length < 100000) {
			Logger.log('no more page : ' + rootHtml)
			break
		}

		// 1次元配列として取得
		for (var si = 0; si < 10000; si++) {
            
			// 商品画像URL
			var itemUrl = searchBy(rootHtml, '<div class="item_imgs" style="background-image:url(',')"', '<section class="search_item_list_section" data-an-comp="search_item">', 10000, si)
            values[currentRowIndex - 2].push(itemUrl ? itemUrl.trim() : '該当データ無し')                
            
			// 商品画像
            var i = si + 2
            var itemImage = '=IMAGE(B' + parseInt(i) + ',4,100,50)'
            values[currentRowIndex - 2].push(itemImage ? itemImage.trim() : '該当データ無し')  

　　　　　currentRowIndex++
    }

		// 2秒~5秒スリープ
		Utilities.sleep(2000 + Math.floor(Math.random() * (2999)) + 1)
	}
	
	// 一括でインサート
	if (values && values[0]) {
		sheetData.getRange(SEARCH_START_LOW, SEARCH_COL_INDEX, values.length, values[0].length).setValues(values)
	}
}   

発生している問題

var itemImage = '=IMAGE(B' + parseInt(i) + ',4,100,50)'

↑これにより出力されるデータが
↓このようになってしまいます

=IMAGE(B2,4,100,50)
=IMAGE(B3,4,100,50)
=IMAGE(B4,4,100,50)
=IMAGE(B5,4,100,50)
=IMAGE(B6,4,100,50)
=IMAGE(B7,4,100,50)
=IMAGE(B8,4,100,50)
=IMAGE(B9,4,100,50)
=IMAGE(B10,4,100,50)
=IMAGE(B11,4,100,50)　←B１１までは正しく表示
=IMAGE(B2,4,100,50)　←本来であればB１２になるはずがB2に戻ってしまう
=IMAGE(B3,4,100,50)　←続けて問題ありそれ以降も問題あり
=IMAGE(B4,4,100,50)
=IMAGE(B5,4,100,50)
=IMAGE(B6,4,100,50)
=IMAGE(B7,4,100,50)
=IMAGE(B8,4,100,50)
=IMAGE(B9,4,100,50)
=IMAGE(B10,4,100,50)
=IMAGE(B11,4,100,50)
・
・
・
=IMAGE(B2,4,100,50)　←ひたすらB2~B11が繰り返されてしまいます
=IMAGE(B3,4,100,50)
=IMAGE(B4,4,100,50)
=IMAGE(B5,4,100,50)
=IMAGE(B6,4,100,50)
=IMAGE(B7,4,100,50)
=IMAGE(B8,4,100,50)
=IMAGE(B9,4,100,50)
=IMAGE(B10,4,100,50)
=IMAGE(B11,4,100,50)

試したこと

ネットで問題を探して、文字列と数値の変換がうまくいっていない可能性があると思い、
parseInt関数やダブルコーテーションの変更など試しましたがうまくいきませんでした。
↓これが一応試してみたものになります。

var i = parseInt(si) + 2
var i = parseInt(si) + "2"
var i = parseInt("si") + 2
var i = parseInt("si") + "2"

var i = si + 2
var i = si + "2"

for (var si = "0"; si < "10000"; si++) {

###補足情報
function searchByの独自関数は問題なく機能しています。

###papinianusの質問に回答します。
currentRowIndex++のインデント位置からしてifかなにかを削っているはず。に関してですが、商品画像URLの前にASINコードを用意しています。

・
・
・
		for (var si = 0; si < 10000; si++) {
			// ASIN
			var asin = searchBy(rootHtml, '<div data-caution="', '">', 'class="search_item_list_section"', 100000, si)
			if (!asin) {
				break
			}
			values[currentRowIndex - 2] = []
			values[currentRowIndex - 2].push(asin || '該当データ無し')
            
			// 商品画像URL
・
・
・

searchByに関して追記します。

/*
 target            検索対象
 startKey          検索開始文字列
 endKey            検索終了文字列
 searchStartKey    検索対象開始文字列
                   ※なしの場合は検索対象全てから検索
 searchLength      検索対象文字数
                   ※なしの場合は検索対象開始文字列以降全てから検索
 searchRepeat      検索繰り返し回数
 */
function searchBy (target, startKey, endKey, searchStartKey, searchLength, searchRepeat) {
	// 検索開始文字列があった場合のみ
	if (searchStartKey) {
		var index = target.indexOf(searchStartKey)
		for (var c = 0; searchRepeat && c < searchRepeat; c++) {
			index = target.indexOf(searchStartKey, index + 1)
		}

		if (index === -1) {
			Logger.log("searchStartKey not found")
			return null
		}

		// 指定文字数取得して検索対象を更新
		target = target.substring(index + searchStartKey.length,
			searchLength ? index + searchStartKey.length + searchLength : target.length)
	}

	var startIndex = startKey !== null ? target.indexOf(startKey) + startKey.length : 0
	var endIndex = endKey !== null ? target.indexOf(endKey, startIndex) : target.length

	if ((startKey === null || startIndex !== (startKey.length - 1)) && endIndex !== -1) {
		return target.substring(startIndex, endIndex)
	} else {
		return null
	}
}

###function test()を実行した際のログを表示します。

[18-10-04 12:47:05:886 JST] searchStartKey not found
[18-10-04 12:47:05:887 JST] asin:null

Comments

[macaron_xxx]

このスクリプトですが、rootHtml.lenght <100000 が常にTrueじゃないですか？　Logger.log('no more page : ' + rootHtml)　が吐かれてそうな気がするのですが…。

[macaron_xxx]

色々関数が入っていて、正確に実行できていないかもしれないですが、私が実行した限り　values[currentRowIndex - 2]　がundefined　で実行できません。

[papinianus]

searchByは無関係、siも、iもnumber型だからparseIntも無関係。currentRowIndex++のインデント位置からしてifかなにかを削っているはず。だからmacaron_xxxさんは動作させることもできない。おそらくそこにvalues.push([])みたいなコードがあると思われる。その間違った関数は全体で何行出力されていますか？

[ryu01212008]

papinianusさん回答ありがとうございます。currentRowIndex++のインデント位置からしてifかなにかを削っているはず。に関しては追記させていただいたので確認ください。

[ryu01212008]

values.push([])みたいなコードがあると思われる。その間違った関数は全体で何行出力されていますか？に関しては、values[currentRowIndex - 2].push()のことでしょうか？

[papinianus]

間違った関数はB2で繰り返されるIMAGEのことでした。改行を入れられず誤解を招く文章でした、すみません。追記感謝

Answer 1

// 商品画像
var i = (sc - 1) * 10 + si + 2

で意図した動きになると思います。

Answer 2

他人の回答と同じになっても回答削除できないのがteratailの仕様(以前削除依頼したが拒絶された)なので、パクリになりますが、動作するような修正をします。

    // 検索
    for (var sc = 1; sc <= 100000; sc++) {
        var rootHtml = getHtml('サイトのURL' + sc)

        if (!rootHtml || rootHtml.length < 100000) {
            Logger.log('no more page : ' + rootHtml)
            break
        }

        // 1次元配列として取得
        for (var si = 0; si < 10; si++) {//ここ(1ページ10件であることが前提)
            // ASIN
            var asin = searchBy(rootHtml, '<div data-caution="', '">', 'class="search_item_list_section"', 100000, si)
            if (!asin) {
                break
            }
            values[currentRowIndex - 2] = [];
            values[currentRowIndex - 2].push(asin || '該当データ無し');
            // 商品画像URL
            var itemUrl = searchBy(rootHtml, '<div class="item_imgs" style="background-image:url(',')"', '<section class="search_item_list_section" data-an-comp="search_item">', 10000, si);
            values[currentRowIndex - 2].push(itemUrl ? itemUrl.trim() : '該当データ無し')                

            // 商品画像
            var i = (sc - 1) * 10 + si + 2;
            var itemImage = '=IMAGE(B' + i + ',4,100,50)'
            values[currentRowIndex - 2].push(itemImage ? itemImage.trim() : '該当データ無し');
         }
　　　　　currentRowIndex++
    }

Comments

[papinianus]

入れ違いでmacaron_xxxさんの回答に補足なさったとおり、やはりページネーションですね。
siのロジックバグに当初気づけずあさっての回答をし、不要な情報を要求し、解決に回り道となったことお詫びします。
多分これでいけるはず

[macaron_xxx]

これはもとのソースと何もかわっていないのでは、と思うのですが…。

[papinianus]

そうか、scの外でリセットしないといけないのですね

[macaron_xxx]

いや、問題はsiのリセットの場所ではないです。
私の回答のコメントを読んでいただけるとわかるかと。

[papinianus]

siをページ単位の数と思ってなかったんですが、searchByでsiを使っているから、だめってことなんですね。
なんだかだめっすね

[macaron_xxx]

>上記 sc は si の誤植だと思うので、そう認識してコメントしますね。

searchByでsiを使っているからダメというわけではありませんよ。
結局のところ、i が si に依存している以上、si が 10件しかないのは事実です。
（これはログを取得したことでわかりました。）

加えて、ページネーションという情報とgetHtmlにscを付与していることから、scはページ番号だと言うことがわかり（推測でき）ました。
そうすると、あとは i は si だけでなく sc にも依存させる必要があるということがわかります。

私にはページネーションという着眼点がなかったので、それに気づけたpapinianusさんはさすがだなと思いましたよ。

[papinianus]

結局ここで作りたかったであろうものにおいては、
* iは商品のトータルにおける件数(+2) // +2は、getRangeが1-indexedで、かつヘッダがあるから。本質的には、iはトータル件数(通し番号？)に依存している
* scがページ
* siがページ内のアイテム数
ということですよね(すでに、ここが違うのでしょうか)？

私は
* sc = 件数
* si = トータル件数(通し番号？) // こう思ったのはsiの上限値が多いので、siはトータルを表現したかったんじゃないの的な憶測をしたからです
* i = getRangeにかませるために2足す便宜変数
だという回答をし(ようとし)ていました。
ただ、"ページ内のアイテム数"を表す変数がないとdivか何かを取れないので、結局方針に問題があったのだという自省をしました。
この辺をまとめると「searchByにおいてsiが必要だから～」という表現になっていました。

[ryu01212008]

お二方ともありがとうございます！
12以降も表示されるようになったのですが、今度は22以降がそもそも出力されなくなってしまいました。
原因はなんでしょうか？

[macaron_xxx]

> papinianusさん
sc は 1 始まりですので、
var i = (sc - 1) * 10 + si + 2
です。

[macaron_xxx]

＞ryu01212008さん
元のソースに対して
var i = (sc - 1) * 10 + si + 2
しか変えていないとすると、22以降が表示されないということはありえないと思います。

他に何か変えていませんか？
今の実行コードを記載ください。

[papinianus]

iの算出箇所は修正しました。

もしかして、上記のコードをコピーしている？でも、結局のところ変えたのは、二次元目のループの上限値と、var iのところだけのつもり。でも細切れで提示されたのを私の独断で組み合わせているから、そのままコピペだとだめなのかも。

[ryu01212008]

全て元の形に戻した後に、var i = (sc - 1) * 10 + si + 2だけ変更したところ思い通りの結果になりました！
ありがとうございました！

Answer 3

下記のスクリプトの部分でsi=10のときにasinがfalseとなりえる値をとっているとしか考えられません。

var asin = searchBy(rootHtml, '<div data-caution="', '">', 'class="search_item_list_section"', 100000, si)
if (!asin) {
  break
}

searchByという名前から何かデータから検索しているのだろうと考えられますが、si=10のときに正しく動いているかを確認するほうがよいですね。

function test() {
  var rootHtml = getHtml('サイトのURL' + 1)
  var asin = searchBy(rootHtml, '<div data-caution="', '">', 'class="search_item_list_section"', 100000, 10)
  Logger.log("asin:%s",asin)
}

追記

上記test()を実行していただいて、ログを確認してみてください。
おそらくasin:nullが出力されているかと思います。

その場合、searchStartKey not foundもログに出力されているか確認してください。
（おそらく出力されていないはず）

出力されていなければ、
searchBy関数に下記を足して、ログをみてみてください。

function searchBy (target, startKey, endKey, searchStartKey, searchLength, searchRepeat) {
  /*** ここまで省略 ***/
  var startIndex = startKey !== null ? target.indexOf(startKey) + startKey.length : 0
  var endIndex = endKey !== null ? target.indexOf(endKey, startIndex) : target.length

  // ログの出力を追加
  Logger.log("startKey:%s, startIndex:%s, startKey.length:%s, endIndex:%s",startKey, startIndex, startKey.length, endIndex)

  if ((startKey === null || startIndex !== (startKey.length - 1)) && endIndex !== -1) {
    return target.substring(startIndex, endIndex)
  } else {
    return null
  }
}

おそらく、このログで何が原因で止まっているかがわかるかと思うのですが…。

Comments

[ryu01212008]

searchByに関して内容の追記をさせていただきました。

[macaron_xxx]

追記しましたので、ご確認の上、ログを貼り付けていただければと思います。

[ryu01212008]

function test()を実行した結果を追記しました。原因は何になるのでしょうか？

[macaron_xxx]

searchBy()が正常に機能しているとおっしゃっているので、
target（rootHtml）に
class="search_item_list_section"
が10コしか含まれていない、ということです。

つまり、実行したいことと、記述しているコードに乖離があることが原因です。

この先は一体このスクリプトで何がしたいのかを明確にしていただけないとサポートできません。

[macaron_xxx]

getHtmlの中身がわかると、処理の全体が見えるかも？

[ryu01212008]

モノレートと言われるサイトの検索結果ページをページネーションしながら内容のスクレーピングをしたいんです。
https://mnrate.com/search?i=All&kwd=%E3%82%A2%E3%83%B3%E3%83%91%E3%83%B3%E3%83%9E%E3%83%B3&s=
例えば、この一覧ページです。

[macaron_xxx]

siの最大値は1ページにある画像の個数（10件）
scの最大値は総ページ数ですよね。

で、i = si + 2なので
var itemImage = '=IMAGE(B' + parseInt(i) + ',4,100,50)'
の結果は10ずつになるのはやはり仕方いないんですよね。
（だって、si は 0 ~ 9 の値しか取りえないので, i もそれに2を加えた 2 ~ 11 にしかならない）

でも実現したいのは、Bは全ての画像の通し番号のようなものです。
そうすると sc にも依存するようになり
10×sc（ページ数）+ si（そのページの画像の順番）
となりますね。

つまり
i = (sc - 1) * 10 + si + 2;
とすればよいのではないでしょうか。

Answer 4

searchByおよび対象ページが不明なので断言はできませんが、B2からB11で繰り返すのはasinが10個しか取得できず!asinがtrueになってbreakが実行されているから、と考えられます。

この推測が正しいかどうかについては、IMAGE関数が出力されている行数と、scの個数かける10が等しいかどうかで検証できます

Comments

[ryu01212008]

searchByに関して内容の追記をさせていただきました。

[papinianus]

searchByに関して、macaron_xxxさん提示のコードによるログをまちます。同じ情報が私も必要です。

ところで、`searchBy(rootHtml, '<div class="item_imgs" style="background-image:url(',')"', '<section class="search_item_list_section" data-an-comp="search_item">', 10000, si)`のうしろのほうの10000は数字として多くないですか？検索対象文字数がこんなに長いのでしょうか？これほど長いと、siのforループの最後のほうでは、targetからあふれた位置を指してエラーになりそうです。

[ryu01212008]

ログの内容を追記しました。

>10000は数字として多くないですか？
確かに、これは1桁間違えてしまっています。10000ではなく1000の予定でした。

[papinianus]

またしてもmacaron_xxxさんのあとおいになりますが、asinを表現しているクラスが１０個しかないのですね
私はgetHTMLの内容ではなくページ依存だと考えます。対象ページが明らかにならないと多分無理

推測で書きますがgetHTMLでurlfetchしているとしたら、それで取得できる内容は人間がブラウザで見るものとは必ずしも一致しないと思います。特に動的に追加される項目は取れない可能性があり、予想より少なく取れているのかもしれない